topic DAX SUMX Summarize Groups over DatesYTD not working in DAX Commands and Tips

DAX SUMX Summarize Groups over DatesYTD not working

Mel_Kulek — Mon, 06 Nov 2023 14:22:30 GMT

Hello all,

I'm stumbling over a problem now for what seems like half-a-day to no avail. Therefore, I'm hoping someone here can shed some insight that might lead me to my expected result. Thank you in advance if you take the time to read through this issue!

I have a very simple data-model (the actual one is way more complex, this is the simplified one to avoid real data being posted)

The user can select Scenario 1, and Scenario 2 via a slicer (this feature cannot be removed). However, sometimes Scenario 1 will not have results (When aggregated over Category and summed YTD), and thus should not be in the total summation for the final division. The grouping of the result occurs at the Category level, with the figures summed up to the YTD of the Month Slicer (this slicer cannot be removed).

Here you can see the summations (Scn2, Scn1) occurring correctly, however, when I do the summation over a summarize with DATESYTD, I'm getting the wrong value (Green Circle). The expected result is the image with Green rows above the table with partially right and wrong results.

The expected results are shown at the top where I would expect the DatesYTD to work correctly, but for the life of me the summation won't work, to where I can filter out the relevent group-sums. I'm trying to avoid dax'ing a LOOKUPVALUE, as the actual level of dimensions in the real model are quite numerous, whereas in the attached PBIX file its very simple.

test scn1 = VAR _Scn = SELECTEDVALUE('Scenario1'[Scenario1]) VAR _Result = CALCULATE( CALCULATE( SUM('Fact Table'[Value]) ,DATESYTD('Master_Calendar'[Date]) ) ,'Fact Table'[Scenario] = _Scn ) RETURN _Result

test scn2 = VAR _Scn = SELECTEDVALUE('Scenario2'[Scenario2]) VAR _Result = CALCULATE( CALCULATE( SUMX('Fact Table', 'Fact Table'[Value] ) ,DATESYTD('Master_Calendar'[Date]) ) ,'Fact Table'[Scenario] = _Scn ) VAR _Scn1 = [test scn1] RETURN _Result

test scn2 summarize = VAR _Scn = SELECTEDVALUE('Scenario2'[Scenario2]) VAR _Summary = FILTER( SUMMARIZE( 'Fact Table' ,'Fact Table'[Category] ,'Fact Table'[Period] ,"Result Scn1", [test scn1] ,"Result Scn2", [test scn2] ) ,NOT([Result Scn1] in { Blank(), 0} ) ) RETURN SUMX(_Summary, [Result Scn2])

Slightly different version of the above:

test scn1 summarize = VAR _Scn1 = SELECTEDVALUE('Scenario1'[Scenario1]) VAR _Scn2 = SELECTEDVALUE('Scenario2'[Scenario2]) VAR _Summary = FILTER( SUMMARIZE( 'Fact Table' ,'Fact Table'[Category] ,'Fact Table'[Period] ,"Result Scn1" ,CALCULATE( CALCULATE( SUM('Fact Table'[Value]) ,DATESYTD('Master_Calendar'[Date]) ) ,'Fact Table'[Scenario] = _Scn1 ) ,"Result Scn2" ,CALCULATE( CALCULATE( SUM('Fact Table'[Value]) ,DATESYTD('Master_Calendar'[Date]) ) ,'Fact Table'[Scenario] = _Scn2 ) ) ,NOT([Result Scn1] in { Blank(), 0} ) ) RETURN SUMX(_Summary, [Result Scn1])

I realize its an issue with DatesYTD, I've tried numerous other variations, such as FILTER(dates, [Month Number] <= SELECTEDVALUE(dates[Month Number]) ), using GroupBy, SummarizeColumns, and other combinations; however, for the life of me, I'm stuck.

PBIX File Here

Thanks for any help, and hope you have a great day!

Re: DAX SUMX Summarize Groups over DatesYTD not working

MarkLaf — Tue, 07 Nov 2023 03:27:25 GMT

You essentially want to get a Scenario 1 YTD vs Scenario 2 YTD percent, but only for categories that have data in both scenarios, right? I would take a different approach.

First, especially if performance is a concern, set up physical relationships between the Scenarios and your calendar table. Make these relationships inactive. Get rid of your bidirectional filter. At the end of the day, your scenarios are filtering your calendar table; it's more efficient to filter calendar with scenarios directly rather than through your fact.

You can then get what (I think) you want with the following measures:

test scn1 = CALCULATE( SUM( 'Fact Table'[Value] ), CALCULATETABLE( DATESYTD( Master_Calendar[Date] ), USERELATIONSHIP( Master_Calendar[Year], Scenario1[Scenario1] ) ) ) test scn2 = CALCULATE( SUM( 'Fact Table'[Value] ), CALCULATETABLE( DATESYTD( Master_Calendar[Date] ), USERELATIONSHIP( Master_Calendar[Year], Scenario2[Scenario2] ) ) ) scn1 v scn2 = CALCULATE( DIVIDE( [test scn2] - [test scn1], [test scn1] ), // following two filter args will intersect to get cats that have data in both scns CALCULATETABLE( VALUES( 'Fact Table'[Category] ), CALCULATETABLE( DATESYTD( Master_Calendar[Date] ), USERELATIONSHIP( Master_Calendar[Year], Scenario1[Scenario1] ) ) ), CALCULATETABLE( VALUES( 'Fact Table'[Category] ), CALCULATETABLE( DATESYTD( Master_Calendar[Date] ), USERELATIONSHIP( Master_Calendar[Year], Scenario2[Scenario2] ) ) ) )

Result:

Re: DAX SUMX Summarize Groups over DatesYTD not working

Mel_Kulek — Tue, 07 Nov 2023 07:17:29 GMT

MarkLaf,

Thank you for your support. However, this solution won't work.

There are multiple scenarios that can exist in a particular year, e.g. 2022 v1, 2022 v2, 2022 v3. I should have clarified that in the original scope, it was overlooked because I was trying to keep everything simple.
changing the bi-directional filter will cause a re-write of about 95% of the rest of the report, which I would prefer to avoid. I inherently built the model taking into consideration the multiple scenarios per year, just this particular issue is the one case where it doesn't seemingly work.

I do like your solution and for other projects I might incorporate this, it's unfortunate that I cannot use it in its entirety to solve this particular problem.

Thanks!

Mel