Hi,
I am new at this. I have 3 tables: Sales, Budget and a date table.
Through a slicer the user selects the years they want to see and in visuals they should have Sales for selected years and Budget only for the last year selected.
If they select 2020,2021 and 2022 it should display Sales in 2020,2021 and 2022 and Budget in 2022.
How do I write in DAX the formula to only calculate the sum of budget for the max year that was selected and if there is no budget set for that year to return 0?
Sales= SUM('Invoices'[Cost])
Budget = SUM('Budget'[Value])
This is my Date table:
Date =
VAR StartDate = DATE ( YEAR ( MIN ('Invoices'[Date]) ), 1,1)
VAR EndDate = DATE ( YEAR ( MAX ('Invoices'[Date] )), 12, 31)
return
ADDCOLUMNS(CALENDAR(
StartDate,
EndDate),
"DateAsInteger", FORMAT ( [Date], "YYYYMMDD" ),
"Year", YEAR ( [Date] ),
"MonthNo", FORMAT ( [Date], "MM" ),
"YearMonthNo", FORMAT ( [Date], "YYYY/MM" ),
"YearMonth", FORMAT ( [Date], "YYYY/mmm" ),
"MonthShort", FORMAT ( [Date], "mmm" ),
"MonthLong", FORMAT ( [Date], "mmmm" ),
"WeekNo", WEEKDAY ( [Date] ),
"WeekDay", FORMAT ( [Date], "dddd" ),
"WeekDayShort", FORMAT ( [Date], "dddd" ),
"Quarter", "Q" & FORMAT ( [Date], "Q" ),
"YearQuarter", FORMAT ( [Date], "YYYY" ) & "/Q" & FORMAT ( [Date],"Q" )
)
Your help is very much appreciated!
