topic Re: Help in Cumulative Total in DAX Commands and Tips

Help in Cumulative Total

apatwal — Wed, 02 Mar 2022 17:35:01 GMT

I am facing issues with Cumulative Total. Below are the DAX I am using for cumulative total.

I have below table

Week Start Date | Margin | Margin Last Year Week | Difference in Margin

I have to take Cumulative of Difference in Margin

Margin is measure

Margin Last Year Week is measure calculated as

Margin Last Year Week =

CALCULATE(

[Margin],

FILTER(

ALL('Date'),

'Date'[Week Rank] = MAX('Date'[Week Rank])-52

)

Difference in Margin is a measure which is calculated as

Difference in Margin =

CALCULATE(

([Margin] - [Margin Last Year Week]),

FILTER('Date','Date'[Year] = 2022)

)

Cumulative Total DAX

Margin Diff Cumulative =

CALCULATE(

[Difference in Margin],

FILTER(

ALL('Date'),

'Date'[Date] <= MAX('Date'[Date])

)

We have neagative values from March as we don't have data from March 2022 (difference is calculated by taking difference of previous year week from current year week in focus.)

so Feb cumulative value should be 4997451 + 2398854 = 7396305 ( which is coming as 7494045 which is wrong )

I want to display this Cumulative value in column chart and also I want that it should display till Feb without using any invoice filter as we have data from Jan 2021 to Feb 2022 and data is populated on weekly basis so this graph will gradually increases weekly.

Any help will be appreciated.

Thanks!

Re: Help in Cumulative Total

tamerj1 — Wed, 02 Mar 2022 18:27:28 GMT

Hi apatwal
Try to use Year-Week number instead of week number

I know you are filtering only 2022 but I would be more comfortable with slicing by Year-Month instead of Month Name.

Re: Help in Cumulative Total

Whitewater100 — Wed, 02 Mar 2022 19:55:40 GMT

Hi:

I agree with tamerj on using Yr-Week No.

To get your graph to display correctly you can do your measure like this:

Measure = IF( ISBLANK([Margin], BLANK(), [Incremental Margin Difference]))

Re: Help in Cumulative Total

apatwal — Thu, 03 Mar 2022 05:38:57 GMT

Hi tamerj1

I had tried using Month Name which is correct showig only filtered data but my cumulative totals are coming wrong.

I am calculating margin difference week wise and then taking cumulative month wise.

Can you be more specific where to use Year-Week number; I am confused here.

I have given below DAX to calculate Week Rank

Week Rank =

RANKX(

ALL('Date'),

'Date'[Week Start Date],,

ASC,

DENSE

)

Appreciate your response!

Re: Help in Cumulative Total

tamerj1 — Thu, 03 Mar 2022 05:59:22 GMT

Hi apatwal
Your measure is

Margin Last Year Week = CALCULATE ( [Margin], FILTER ( ALL ( 'Date' ), 'Date'[Week Rank] = MAX ( 'Date'[Week Rank] ) - 52 ) )

What is the Week Rank column? Is it the week number from 1 - 52 or a unique number that do not recreate for other years

Re: Help in Cumulative Total

apatwal — Thu, 03 Mar 2022 07:08:32 GMT

Hi tamerj1

Week Rank Column is in Date Table that is a unique number that do not recreate for other years. Below is the DAX used to create Week Rank Column

Week Rank =

RANKX(

ALL('Date Table'),

'Date Table'[Week Start Date],,

ASC,

DENSE

)

For Jan 2021

For Jan 2022

Let me know if I am doing something wrong here...

As my cumulative values are coming wrong...

Re: Help in Cumulative Total

tamerj1 — Thu, 03 Mar 2022 07:30:34 GMT

Nothing wrong with that. To fix the total issue:

step 1 replace MAX with MIN and spot any difference in the montly values. If so then you have issues with your data that need to be fixed before moving forward. If nothing change at month level the move to step 2

step2: create a new measure

New Measure =
SUMX (

VALUES ( Date[Year Month] ),

[Old Measure]
]
step3: use the new measure in your visual instead of the old one.

Re: Help in Cumulative Total

Anonymous — Mon, 07 Mar 2022 02:39:21 GMT

Hi apatwal ,

Is your problem solved, if not, Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data. We can better understand the problem and help you.

For problems with totals in your question, you can create a measure.

Sum_Total = var _table=SUMMARIZE('Date', 'Date' [Month Year],"_value",[Margin Difference Cumlative]) return IF(HASONEVALUE('Date' [Month Year]),[ Margin Difference Cumlative],SUMX( _table,[_value]))

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

Re: Help in Cumulative Total

apatwal — Mon, 07 Mar 2022 09:26:06 GMT

Hi Anonymous

Please find the attached PBI Sample file

Sample file

I had calculated weekly difference of Margin and want cumulative sum Month wise.

Let me know if you need any futher information.

Re: Help in Cumulative Total

tamerj1 — Mon, 07 Mar 2022 09:58:32 GMT

Hi apatwal
Please refer to the sample file with the solution https://www.dropbox.com/t/D5m4mMmNS3CVhZA1
The measures are

Difference in Margin = SUMX ( VALUES ( 'Date'[Month Year] ), CALCULATE ( IF ( NOT ISBLANK ( SUM ( Margin[Margin] ) ), CALCULATE ( SUM ( [Margin] ) - [Margin Last Year Week], 'Date'[Year] = 2022 ) ) ) )

Margin Diff Cumulative = IF ( NOT ISBLANK ( [Difference in Margin] ), CALCULATE ( [Difference in Margin], REMOVEFILTERS ( 'Date' ), 'Date'[Date] <= MAX ( 'Date'[Date] ) ) )

Re: Help in Cumulative Total

apatwal — Tue, 08 Mar 2022 14:20:59 GMT

Hi tamerj1

This works perfectly fine for me! Thanks for your help.

But there is some changes which are needed.

If you see in below screenshot, to calculate difference in margin, the last value of Feb 28 is being considered to calculate difference rather than addition of all values.

It should be 195325 - 195462 (total of all Margin Last Year Week for a month)

Also, if we do not have Margin values then it should not be considered while calculating difference. Like in below, Difference for March month should be 6995.

below DAX is being used to calculate Margin Last year week.

Margin Last Year Week =

CALCULATE(

[Margin],

FILTER(

ALL('Date Table'),

'Date Table'[Week Rank] = MAX('Date Table'[Week Rank])-52

)

Re: Help in Cumulative Total

tamerj1 — Tue, 08 Mar 2022 16:21:08 GMT

apatwal
No it is not working perfectly! Actually all the numbers were wrong as I did not pay attention the first measure [Margin Last Year Week].
The following should work

Margin Last Year Week = SUMX ( VALUES ('Date'[Week Rank] ), CALCULATE ( IF ( NOT ISBLANK ( SUM ( Margin[Margin] ) ), CALCULATE ( SUM ( Margin[Margin] ), REMOVEFILTERS ( 'Date' ), 'Date'[Week Rank] = MAX ( 'Date'[Week Rank] ) - 52 ) ) ) )

Difference in Margin = SUMX ( VALUES ( 'Date'[Week Rank] ), CALCULATE ( IF ( NOT ISBLANK ( SUM ( Margin[Margin] ) ), CALCULATE ( SUM ( [Margin] ) - [Margin Last Year Week], 'Date'[Year] = 2022 ) ) ) )

Margin Diff Cumulative = SUMX ( VALUES ('Date'[Week Rank] ), CALCULATE ( IF ( NOT ISBLANK ( [Difference in Margin] ), CALCULATE ( [Difference in Margin], REMOVEFILTERS ( 'Date' ), 'Date'[Date] <= MAX ( 'Date'[Date] ) ) ) ) )

Still not sure about the blanks issue as my sample data don't have blanks. So please check and let me know. Thank you.

Re: Help in Cumulative Total

apatwal — Wed, 09 Mar 2022 10:34:37 GMT

Hi tamerj1

Thanks for your reply!

In below screenshot you can see that Jan 31 (week start date) is mapped to both Jan and Feb and same for Feb 28 which is mapped to Feb and March.

This is because Jan 31 which is week start date includes Feb month dates. Is there any possibilty that Jan 31 entire week should be entirely mapped to Jan month and same for Feb 28 and for every month end.

Also, your Cumulative measure does not give correct result it looks it is doing cumulative sum weekly basis but we need cumulative sum on month basis.

If we change VALUES('Date'[Week Rank]') to VALUES('Date'[Month Year]) that would work.

Thanks!

Re: Help in Cumulative Total

tamerj1 — Wed, 09 Mar 2022 11:07:17 GMT

apatwal
Regarding your first concern. Yes this is reasonable because the week that starts on Jan. 31 ends on Feb. 06 and the week that starts on Feb. 28 ends on Mar. 06 therefore both weeks exists in two months. This is also related to the 2nd concern. You are trying to slice by weeks and month at the same time, which is not an easy task. Handling weeks requires extra attention and different approach.
Now allow me to go back again to your 1st concern. If you remove the Month Year column from the table visual the problem will be solved.
If you are ok with the then I can update the code so it can decide automatically either to iterate over weeks of over months. Thank you

Re: Help in Cumulative Total

tamerj1 — Wed, 09 Mar 2022 11:41:44 GMT

apatwal
This is the updated sample file https://www.dropbox.com/t/zS12j58c8c1BlRNY
The final measure are

Margin Diff Cumulative = SUMX ( VALUES ('Date'[Month Year] ), CALCULATE ( IF ( NOT ISBLANK ( [Difference in Margin] ), CALCULATE ( [Difference in Margin], REMOVEFILTERS ( 'Date' ), 'Date'[Date] <= MAX ( 'Date'[Date] ) ) ) ) )

Thank you!

Re: Help in Cumulative Total

apatwal — Wed, 09 Mar 2022 12:14:25 GMT

Hi tamerj1

Thanks for your reply!

I changed my measures as per your guidance but if you see below screenshot, my cumulatives values are taking duplicate values at the month end

It should be as below:

Jan 2022 - 1307920

Feb 2022 - 2297153 (but in above ss, it is +84645 From Jan 31)

Mar 2022 - 2407155 (from above ss, it is +84645 From Jan 31 and +118894 from Feb 28)

This is the main issue that needs to be resolved.

Re: Help in Cumulative Total

tamerj1 — Wed, 09 Mar 2022 12:44:31 GMT

apatwal
You are are right as both months are visible in the filter context.
I don't have this issue in the sample file. I guess I don't have enough data. However, I hope the following code will solve this problem and it will also remove the grand total which has no meaning anyway. Please try and let me know:

Margin Diff Cumulative = VAR LastMonthInFilter = MAX ('Date'[Month Year] ) VAR IterationTable = FILTER ( VALUES ('Date'[Month Year] ), 'Date'[Month Year] = LastMonthInFilter ) RETURN SUMX ( IterationTable, CALCULATE ( IF ( NOT ISBLANK ( [Difference in Margin] ), CALCULATE ( [Difference in Margin], REMOVEFILTERS ( 'Date' ), 'Date'[Date] <= MAX ( 'Date'[Date] ) ) ) ) )

Re: Help in Cumulative Total

apatwal — Wed, 09 Mar 2022 13:09:12 GMT

Hi tamerj1

Thanks for your reply!

This had not resolved the issue. Still duplicate value is being taken at the end of month. Please find the below snapshot

I am pasting DAX which is being used:

Margin Last Year Week = SUMX( VALUES('Date'[Week Rank]), CALCULATE( IF( NOT ISBLANK( SUM(Margin)), CALCULATE( SUM(Margin), REMOVEFILTERS('Date'), 'Date'[Week Rank] = MAX('Date'[Week Rank])-52 ) ) ) )

Difference in Margin = SUMX( VALUES('Date'[Week Rank]), CALCULATE( IF( NOT ISBLANK(SUM(Margin)), CALCULATE( SUM(Margin) - [Margin Last Year Week], 'Date'[Year] = 2022 ) ) ) )

Margin Diff Cumulative is the same measure you have shared.

Re: Help in Cumulative Total

tamerj1 — Wed, 09 Mar 2022 13:20:33 GMT

Hi apatwal

can you please share the sample updated with at least one more month of data?

Re: Help in Cumulative Total

tamerj1 — Wed, 09 Mar 2022 13:23:38 GMT

Hi apatwal

can you please share the sample updated with at least one more month of data?