Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

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
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.