Dynamic Seasonal Date Range

DAX DAX Calculations
1

Hi All, I’m working on a report to calculate a yearly running total between two specific date range; the second Monday in September and January 31st of the next year, and want it to be dynamic for coming years.

I’m presently using this code for the Running Total.

Running Total For The Season ($) = TOTALYTD( SUM(Seasonal[Amount ($)]), 'Date'[Date], 'Date'[Month] = 9 || 'Date'[Month] = 10 || 'Date'[Month] = 11 || 'Date'[Month] = 12 || 'Date'[Month] = 1, "1/31" )

RunningTotalSample.pbix (232.7 KB)
ExportExcel.xlsx (230.8 KB)

The pbix and excel files are also attached.

Thank you

2

@zeekstuarts ,

How tied are you to the DAX CALENDARAUTO Date Table that you are using? If you were open to switching over to @Melissa 's awesome Extended Date Table, it would make this analysis a snap.

  • Brian
2 Likes
3

I can change the date table to that of @Melissa’s. Will like to know why calendarauto function won’t work though. Thanks for helping out.

4

@zeekstuarts ,

The main reason is that there’s a nested group manipulation I’d like to do in Power Query that will make this really easy, and that can only be done with physical tables not DAX calculated tables.

I’ll go ahead and put together a solution on this based on that approach. I just wanted to check with you first to make sure that changing the Dates table wasn’t a non-starter for you.

  • Brian
1 Like
5

Thank you @BrianJ

6

@zeekstuarts ,

Okay, even with the Extended Date Table this ended up being far from a snap, but I think without that table it would’ve been damn near impossible. I ended up using a three-step process:

  1. Grouped by in Power Query

image
image1049×740 15.4 KB

  1. added a custom column index to count the nested table rows in order to identify the second Monday in September

image

  1. build out the following DAX measure. Ended up being quite challenging to get the context correct with the year crossing the December to January line and still picking up the January dates while getting the cumulative function to reset each February 1. Here’s the measure that does the heavy lifting:

Cumul Seasonal2 =

VAR CurrYr = SELECTEDVALUE( Dates[FY 4Digit] )

VAR Result = 

CALCULATE(
    [Total Amount],
    FILTER(
        ALL(Dates),
        Dates[Date] >= [Second September] &&
        Dates[Date] <= [End of Jan Next Year] &&
        Dates[Date] <= MAX( Dates[Date] ) &&
        Dates[FY 4Digit] = CurrYr
    )
)

VAR FiltResult =
IF( 
    SELECTEDVALUE( Dates[Date] )>= [Second September] &&
        SELECTEDVALUE(Dates[Date]) <= [End of Jan Next Year],
        Result,
        BLANK()
)

RETURN FiltResult

Here’s what it looks like all put together:

image

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

– Brian
eDNA Forum - RunningTotalSample Solution.pbix (362.5 KB)

4 Likes
7

@BrianJ thanks so much for this. It’s exactly what I wanted.

1 Like
8

@zeekstuarts ,

Awesome - great to hear that worked well for you.

Really interesting problem. This one was a lot of fun (at least the parts when I wasn’t shouting “why the $&*@ doesn’t that work?!..” at my screen)

  • Brian
1 Like