cancel
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.

Reply
kav_cris
Frequent Visitor

Can't get END of current month in YTD context

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)

 

0 REPLIES 0

Helpful resources

Announcements
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