Calculating YTD thru End of Current Month

DAX DAX Calculations
4

@Whitewater100,

Hopefully this provides a clearer illustration of the approach I was laying out in my prior post:

image
image997×259 15.7 KB

The first step is harvesting the dates from the disconnected slicer:

Harvest Max Date = 
CALCULATE(
    MAX( 'Disconn Date'[Date] ),
    ALLSELECTED( 'Disconn Date'[Date] )
)

then we calculate the final date of the month from the max harvest measure:

Harvest EOM Date = 
EOMONTH( [Harvest Max Date], 0 )

And finally we use that to filter the total open orders measure:

Total Open Orders Disconn Slicer EOM = 
CALCULATE(
    [Total Open Orders],
    FILTER(
        ALL( Dates ),
        Dates[Date] >= [Harvest Min Date] &&
        Dates[Date] <= [Harvest EOM Date]
    )
)

It’s a pretty thorny problem if you try to do it all at once, but breaking it down by measure branching really simplifies the problem.

I hope this is helpful. Full solution file attached below.

P.S. others may disagree, but personally I find the array of DAX time intelligence functions dizzying. I can never remember the difference between PARALLELPERIOD and SAMEPERIODLASTYEAR or DATESBETWEEN and DATESINPERIOD, etc. Other than DATEADD and the EO series, I generally don’t use any of them and just “roll my own” using the general measure branching approach above.

2 Likes