Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.
Hi,
I have this DAX:
VAR SelectedStartDate = STARTOFMONTH(('Date'[Date]))
VAR SelectedEndDate = MAX('Date'[Date])
VAR BudgetDates =
CALCULATETABLE(
'Date',
DATESBETWEEN('Date'[Date], SelectedStartDate, SelectedEndDate),
'Date'[WorkdayID] = 1
)
It works fine when I calculate all the BudgetDates in current month.
However, when I want the YearToDate Budget values (using YTD in a filter), SelectedStartDate is right (1st of Jan), but SelectedEndDate becomes the end of Jan (I want the dates between 1st of Jan and the current month).
I tried making SelectedEndDate = Today() or MAX(Date) but it doesn't work.
Any tips?
For Context. this is the whole formula:
VAR FirstMnthDate = STARTOFMONTH('Date'[Date])
VAR LastMntDate = MAX('Date'[Date])
VAR BudgetDates = CALCULATETABLE('Date', DATESBETWEEN('Date'[Date], FirstMnthDate, LastMntDate), 'Date'[WorkdayID] = 1)
VAR BudgetToDivide = CALCULATE(CALCULATE([Budget], 'Date'[Date] = FirstMnthDate), BudgetDates)
VAR NumOfDays = COUNTROWS(BudgetDates)
VAR DailyAmount = DIVIDE ( BudgetToDivide, NumOfDays,0)
RETURN CALCULATE(SUMX(VALUES('Date'[Date]), DailyAmount), 'Date'[WorkdayID] = 1)
Check out the November 2023 Power BI update to learn about new features.