Creating dynamic budget report

[aishak12]

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)

[Anonymous]

HI @aishak12,

I think provide some dummy data with raw table structure and expected results should help us clarify your scenario and test to coding formula on it.

How to Get Your Question Answered Quickly 
Regards,

Xiaoxin Sheng

[PaulDBrown]

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