0
$\begingroup$

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:

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?

$\endgroup$

1 Answer 1

0
$\begingroup$

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.

$\endgroup$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.