Forum Discussion

aishak12's avatar
aishak12
Helper IV
5 years ago

Creating dynamic budget report

Hi,    I'm creating a budget report with Actual and Budgeted numbers. If the month has already passed (JAN - OCT) I want the table matrix to pull Actual numbers that I have created seperate measure...
Need Help
PaulDBrown's avatar
PaulDBrown
Community Champion
5 years ago

aishak12 

 

If you have a calendar table with a YearMonth column (YYYYMM formated as a number) for the dates, you can create measures:

 

Sum of Sales = SUM(Actuals1[Actuals])
Sum Target = SUM(Target2[Target])

 

and the measure to use in your visual:

 

Sales and Budget = 
VAR YMToday = VALUE(FORMAT(TODAY(), "YYYYMM"))
RETURN
IF(MAX('Calendar Table'[YearMonth])< YMToday, [Sum of Sales], [Sum Target])