Forum Discussion

Helper III

4 years ago

Solved

Help in Cumulative Total

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

DAX Commands and Tips

Help required

tamerj1

4 years ago

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

apatwal

Helper III

4 years ago

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

Community Champion

4 years ago

Hi apatwal

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

tamerj1

Community Champion

4 years ago

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

tamerj1

Community Champion

4 years ago

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

tamerj1
Community Champion
4 years ago
Hi apatwal
can you please share the sample updated with at least one more month of data?
apatwal
Helper III
4 years ago
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...
apatwal
Helper III
4 years ago
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
Community Champion
4 years ago
Hi apatwal
did you have the chance to look into my last reply?

Forum Discussion

Help in Cumulative Total

Related content

Cumulative Total

Cumulative Total

SumX? - cumulative running total

Cumulative Total

Cumulative total