Calculating YTD thru End of Current Month

DAX DAX Calculations
1

Hello:

I am trying to calculate orders received with desired ship dates that go thru the end of this month. (The desired ship dates go further out but my question has to do with dynamically capturing orders thru end of current month based on desired ship date)

The table I’m using has todays date as the slicer date. (5-04-2021).
The table is laid out by customer and has a lot of various measures like YTD, MTD, full month last year etc.

The issue I’m having, in this context, us trying to sum up orders received with desired ship dates up to the end of this month. 5-31-2021. The measure to be summed up is Sum(Total Order Amount).

So while the slicer has todays date of 5-04-2021,the result I’m hoping to achieve would be a cumulative amount thru the end of May. I’ve tried PARALELLE PERIOD, DATESBETWEEN, etc. and have had no luck. When I set the slicer to the end of May I do get correct results. I have fields in my tables with end of current month and other helper columns as an FYI. Because the desire is to have figures next to each customer using ALL type functions does not work here.

Butting up against the slicer is the issue.

Any input on this measure is appreciated. I’ll include a small image that gives an idea of the context. Thank you!image for ytd calc

2

@Whitewater100,

The always versatile disconnected table/slicer to the rescue here:

  1. Create a disconnected, one column table using Dates[Date]. Probably best to do it in Power Query, but you can also create a DAX calculated table using VALUES( Dates[Date] )
  2. Create a slicer from this disconnected date table
  3. Create a measure to harvest the date from this disconnected slicer
  4. Use EOMONTH( [Harvest Date], 0 ) to calculate the date of the last day of the month of the selected date
  5. Calculate total orders, filtering your date table, such that the date <= the value of the measure calculated in 4 above) .

I hope this is helpful. If you need more specifics, please post a PBIX file and I’ll be happy to provide the full specific solution.

– Brian

P.S. while there is not a single value date slider, you can cheat to create one using the “After” option, and harvesting the minimum value from that range. (I’ve got a video on this coming out at the end of this week…)

3

Hi Brian:
Thanks so much for jumping in. I put together a makeshift representative pbix so I can see your handiwork!

I’m not 100% understanding so any update to this file is greatly appreciated. BTW I had no problem bringing in a total May 2020 amount using PARALLELPERIOD but got into all sorts of issues with this particularBrian Slicer for Open Order Calcs.pbix (377.5 KB) problem. I’m excited to see a solution as this has kept me awake for long enough:-)

Thank you so much again.

Bill

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
5

Hi Brian:

This is pretty amazing. I really appreciate you breaking it down like you have. What I think I would do is then find a way to hide the slicer when I am using the measures related to the disconnected slicer mixed with basic other measures tied to the time-intel.

I agree, the amount of time intel measures is dizzying and I think I tried almost all of them trying to figure this out.

Great job and very much appreciated. The example files answers all those in between questions!

Best regards,
Bill

1 Like
6

@Whitewater100,

Great - glad that got you what you needed. It’s always a pleasure working through these with you - really appreciate your clear explanations of the problem, mockups of what you’re trying to achieve and the PBIX files. Makes it easy to provide good support.

  • Brian
7

Hi @Whitewater100, did the response provided by @BrianJ help you solve your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark as solution the answer that solved your query. Thanks!

8

Hi @Whitewater100, we’ve noticed that no response has been received from you since the 5th of May. We just want to check if you still need further help with this post? In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved.

9

Hello:

I thought I clicked on solution a few days ago - anyways all set. That was great help here at EDNA!

Thanks,