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 measures for. If the month has yet to pass I want the table matrix to pull budgeted amount (Nov - Dec). Is there any IF statement I can write for this to be possible. 

 

Column's used: 

 

 

Date format:  202001 =  January, 2020 Converted to Hierarchy for (Y,Q,M,D)

 

 

Need Help

2 Replies

  • 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])