Data Science Stack Exchange is a question and answer site for Data science professionals, Machine Learning specialists, and those interested in learning more about the field. It only takes a minute to sign up.
Sign up to join this community
I would like to use a slicer based on the 12-months of a year to calculate the yearly budget up until a specific month and be able to compare it to realised costs. Slicer:
So, if I am in month 8 (August), the realized costs are shown up until then. Now I also want to be able to create a measure based on that slicer input (8). The measure would then calculate the following: 8/12*the budget. I only need the integer 8 to be able to make the calculation.
Any ideas?
Power BI has great time intelligence functions. Google "Power BI date table" to learn more. There's a ton out there. Learning to use a date table is essential in Power BI.
The typical pattern is to create a date table that has the full date as the key, and columns for your slicers. In your case, you would have the Month (or often Month-Year) on the slicer, which should come from a column on the date table. Then you have a relationship between the date table and your data.
In your report, you should have DAX Measures like this:
Cost = SUM('Data table'[Cost])
Cost YTD = TOTALYTD( [Cost], DimDate[Date] )
If your budget is entered by month, then make sure the DimDate table has a relationship to your budget table and just do the same:
Budget = SUM( Budget[Amount] )
Budget YTD = TOTALYTD( [Budget], DimDate[Date] )
If your budget is annual, it may be easier to create monthly values in Power Query than writing the DAX formulas. I would create a table in Power Query that had end of month dates and do a cross join with my budget, then add a column that divided the budget number by 12. Then use the formulas above.
