Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Frequent Visitor

Can't get END of current month in YTD context



I have this DAX:


VAR SelectedStartDate = STARTOFMONTH(('Date'[Date]))
VAR SelectedEndDate = MAX('Date'[Date])

VAR BudgetDates =
        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)



Helpful resources

PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors