Help in Cumulative Total

[apatwal]

Hi

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!

[tamerj1]

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] )
        )
    )

[apatwal]

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

)

)

[tamerj1]

@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.

[apatwal]

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!

[tamerj1]

@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

[tamerj1]

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

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'[Month Year] ),
    CALCULATE (
        IF (
            NOT ISBLANK ( [Difference in Margin] ),
            CALCULATE (
                [Difference in Margin],
                REMOVEFILTERS ( 'Date' ),
                'Date'[Date] <= MAX ( 'Date'[Date] )
            )
        )
    )
)

Thank you!

[apatwal]

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.

[tamerj1]

@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] )
                )
            )
        )
    )

[apatwal]

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.

[tamerj1]

Hi @apatwal 

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

[tamerj1]

Hi @apatwal 

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

[apatwal]

Hi @tamerj1 

Please find the link for sample data this includes the problem that we are facing and all the new measures that we have created.

pbi sample file 

Thanks...

[tamerj1]

Hi @apatwal 
Here is the sample file with the updated solution https://www.dropbox.com/t/EIDdJ3R216uMXPUO
I had re-wrote the measures using a different technique.
Here is the thing: Handling weeks is not straight forward because weeks overlap over different month and different years. Therefore, a special date table shall be utilized to handle this keeping in mind that week based calculations are only applicable for fiscal year rather than calendar year. The structure of this table is designed carefully to handle week based calculations.
Note: I added a copy of this table in the sample file just for your reference. However, I did not use it as for sure you don't have any intention to change your business model.

The new measures are 

Total Margin = SUM ( 'Margin data'[Margin] )

Margin Last Year Week = 
IF (
    [ShowValueForDates],
    SUMX (
        VALUES ( 'Date'[Week Rank] ),
        VAR CurrentWeekRank = 'Date'[Week Rank]
        VAR DaysSelected =
            CALCULATETABLE (
                VALUES ( 'Date'[Day Year Number] ),
               'Date'[DateWithData] = TRUE
            )
        RETURN
            CALCULATE (
                [Total Margin],
                'Date'[Week Rank] = CurrentWeekRank - 52,
                DaysSelected,
                REMOVEFILTERS ( 'Date' )
            )
    )
)

Difference in Margin = 
VAR CurrentYear =
    MAX ( 'Date'[Year] )
VAR CurrentYearMargin =
    CALCULATE (
        [Total Margin],
        'Date'[Year] = CurrentYear
    )
VAR MarginLastYear = [Margin Last Year Week]
RETURN
IF ( 
    NOT ISBLANK ( MarginLastYear ),
    CurrentYearMargin - MarginLastYear
)

Margin Diff Cumulative = 
VAR CurrentYear =
    MAX ( 'Date'[Year] )
VAR LastDayInFilter =
    MAX ( 'Date'[Day Year Number] )
RETURN
IF ( 
    [ShowValueForDates],
    CALCULATE ( 
        [Difference in Margin],
        REMOVEFILTERS ( 'Date' ),
        'Date'[Day Year Number] <= LastDayInFilter,
        'Date'[Year] = CurrentYear
    )
)

[apatwal]

Hi @tamerj1 

Thanks for your help!

I just have a small doubt here that we are not matching with Margin Last Year Week Data. Could you please help me to understand this. If I see the data for week start date 1/11/2021 on date wise we have data which sums up 65373 and this same value should be shown corresponding to 1/10/2022 week start date

Can you have a look into this..

Thanks!

[tamerj1]

Hi @apatwal 

did you have the chance to look into my last reply?

[tamerj1 · Accepted solution]

Hi @apatwal 
Here is the updated file https://www.dropbox.com/t/l8yJiCeJNMa9ItgD
Again you are right. And again I shifted all calculations down to day level in order to obtain correct results dynamically regardless on which level slicing is. Changes are mainly in the last year margin measure. However following you can find the code of all measures and calculated columns
Day Rank column in the date table:

Day Rank = 
RANKX ( 
    'Date',
    'Date'[Date],,
    ASC
)

Then the measures are

Total Margin = SUM ( 'Margin data'[Margin] )

ShowValueForDates = 
VAR LastDateWithData =
    CALCULATE ( 
        MAX ( 'Margin data'[Invoice Date] ), 
        REMOVEFILTERS () 
    )
VAR FirstDateVisible =
    MIN ( 'Date'[Date] )
VAR Result = 
    FirstDateVisible <= LastDateWithData
RETURN
    Result

Margin Same Period Last Year = 
IF (
    [ShowValueForDates],
    VAR FirstDayRanknPeriod = 
        CALCULATE ( 
            MIN ( 'Date'[Day Rank] ),
            'Date'[DateWithData] = TRUE ()
        )
    VAR LastDayRankInPeriod = 
        CALCULATE ( 
            MAX ( 'Date'[Day Rank] ),
            'Date'[DateWithData] = TRUE ()
        )
    RETURN
        CALCULATE (
            [Total Margin],
            REMOVEFILTERS ( 'Date' ),
            'Date'[Day Rank] >= FirstDayRanknPeriod - 364,
            'Date'[Day Rank] <= LastDayRankInPeriod - 364
        )
)

Difference in Margin = 
VAR CurrentYear =
    MAX ( 'Date'[Year] )
VAR CurrentYearMargin =
    CALCULATE (
        [Total Margin],
        'Date'[Year] = CurrentYear
    )
VAR MarginLastYear = 
        [Margin Same Period Last Year]
RETURN
IF ( 
    NOT ISBLANK ( MarginLastYear ),
    CurrentYearMargin - MarginLastYear

Margin Diff Cumulative = 
VAR LastDayInFilter =
    MAX ( 'Date'[Day Rank] )
RETURN
IF ( 
    [ShowValueForDates],
    CALCULATE ( 
        [Difference in Margin],
        REMOVEFILTERS ( 'Date' ),
        'Date'[Day Rank] <= LastDayInFilter
    )
)

[Anonymous]

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

[apatwal]

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.

[Whitewater100]

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]))

[apatwal]

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!