Cumulative Totals MoM

[jalaomar]

Dear Experts,

I need your support on a problem I am currently facing 

I have combined 2 fact tables and have created a link between these tables through a date table.

In table 1, I am recieving Order intake cumulative Month on Month 

In table 2, I am recieving Order Intake non cumulative value month on month 

so want to show 2 grahps, one showing the the non cumulative vlaues month on month and the other grapgh to show the cumulative value month on month.

see examples below, first table is what I have today in my report 

Year	Month	Table1 Order Intake Actuals Cumulative	Table 2 Order Intake forecast (non cumulative)
2023	Jan	10
2023	Feb	20
2023	Mar	60
2023	Apr	150
2023	Maj	200
2023	Jun	300
2023	Jul		100
2023	Aug		200
2023	Sep		350
2023	Okt		400
2023	Nov		560
2023	Dec		530
Output Non-cumulative Order Intake MoM
Year	Month	Order Intake (non-comulative)
2023	Jan	10
2023	Feb	10
2023	Mar	40
2023	Apr	90
2023	Maj	50
2023	Jun	100
2023	Jul	100
2023	Aug	200
2023	Sep	350
2023	Okt	400
2023	Nov	560
2023	Dec	530
Total		2440
Output cumulative Order Intake MoM
Year	Month	Order Intake (cumulative)
2023	Jan	10
2023	Feb	20
2023	Mar	60
2023	Apr	150
2023	Maj	200
2023	Jun	300
2023	Jul	400
2023	Aug	600
2023	Sep	950
2023	Okt	1350
2023	Nov	1910
2023	Dec	2440

[v-yanjiang-msft · Accepted solution]

Hi @jalaomar ,

According to your description, here's my solution.

Sample:

Table1:

Table2:

Date table:

Relationship:

Create two measures:

Order Intake (non-comulative) =
VAR _VALUE =
    IF (
        HASONEVALUE ( Table1[Order Intake Actuals Cumulative] ),
        MAX ( 'Table1'[Order Intake Actuals Cumulative] )
            - MAXX (
                FILTER (
                    ALL ( 'Table1' ),
                    'Table1'[Order Intake Actuals Cumulative]
                        < MAX ( 'Table1'[Order Intake Actuals Cumulative] )
                ),
                'Table1'[Order Intake Actuals Cumulative]
            ),
        MAX ( 'Table2'[Order Intake forecast (non cumulative)] )
    )
RETURN
    IF (
        ISINSCOPE ( 'Date'[Year] ),
        _VALUE,
        MAX ( 'Table1'[Order Intake Actuals Cumulative] )
            + SUMX ( 'Table2', 'Table2'[Order Intake forecast (non cumulative)] )
    )

Order Intake (cumulative) =
IF (
    HASONEVALUE ( Table1[Order Intake Actuals Cumulative] ),
    MAX ( 'Table1'[Order Intake Actuals Cumulative] ),
    MAXX ( ALL ( 'Table1' ), 'Table1'[Order Intake Actuals Cumulative] )
        + SUMX (
            FILTER ( ALL ( 'Table2' ), 'Table2'[Date] <= MAX ( 'Date'[Date] ) ),
            'Table2'[Order Intake forecast (non cumulative)]
        )
)

Get the correct result:

I attach my sample below for your reference.

Best regards,

Community Support Team_yanjiang

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

[jalaomar]

@v-yanjiang-msft Thank you!! 

[jalaomar]

@v-yanjiang-msft , Hi this is a beautiful solution!!

but doesn't seem to work with my use case, my 2 tables have relationship with the Date Table, single direction and think this is messing up with my measure and can't change to both directions as i have other Dim tables linked as well.

any way to manage this scenario?

Thanks!!

[v-yanjiang-msft · Accepted solution]

Hi @jalaomar ,

I modify the mode to a single direction and here's my solution.

Tweak the measure formula to:

Order Intake (non-comulative) =
VAR _PRE =
    MAXX (
        FILTER ( ALL ( 'Table1' ), 'Table1'[Date] < MAX ( 'Table1'[Date] ) ),
        'Table1'[Date]
    )
VAR _VALUE =
    IF (
        HASONEVALUE ( Table1[Order Intake Actuals Cumulative] ),
        SUM ( 'Table1'[Order Intake Actuals Cumulative] )
            - SUMX (
                FILTER ( ALL ( 'Table1' ), 'Table1'[Date] = _PRE ),
                'Table1'[Order Intake Actuals Cumulative]
            ),
        SUM ( 'Table2'[Order Intake forecast (non cumulative)] )
    )
RETURN
    IF (
        ISINSCOPE ( 'Date'[Year] ),
        _VALUE,
        MAX ( 'Table1'[Order Intake Actuals Cumulative] )
            + SUMX ( 'Table2', 'Table2'[Order Intake forecast (non cumulative)] )
    )

Order Intake (cumulative) =
VAR _MAX =
    MAXX (
        FILTER (
            ALL ( 'Table1' ),
            'Table1'[Order Intake Actuals Cumulative] <> BLANK ()
        ),
        'Table1'[Date]
    )
RETURN
    IF (
        HASONEVALUE ( Table1[Order Intake Actuals Cumulative] ),
        SUM ( 'Table1'[Order Intake Actuals Cumulative] ),
        SUMX (
            FILTER ( ALL ( 'Table1' ), 'Table1'[Date] = _MAX ),
            'Table1'[Order Intake Actuals Cumulative]
        )
            + SUMX (
                FILTER ( ALL ( 'Table2' ), 'Table2'[Date] <= MAX ( 'Date'[Date] ) ),
                'Table2'[Order Intake forecast (non cumulative)]
            )
    )

Get the correct result:

I attach my sample below for your reference.

Best regards,

Community Support Team_yanjiang

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

[v-yanjiang-msft · Accepted solution]

Hi @jalaomar ,

According to your description, here's my solution.

Sample:

Table1:

Table2:

Date table:

Relationship:

Create two measures:

Order Intake (non-comulative) =
VAR _VALUE =
    IF (
        HASONEVALUE ( Table1[Order Intake Actuals Cumulative] ),
        MAX ( 'Table1'[Order Intake Actuals Cumulative] )
            - MAXX (
                FILTER (
                    ALL ( 'Table1' ),
                    'Table1'[Order Intake Actuals Cumulative]
                        < MAX ( 'Table1'[Order Intake Actuals Cumulative] )
                ),
                'Table1'[Order Intake Actuals Cumulative]
            ),
        MAX ( 'Table2'[Order Intake forecast (non cumulative)] )
    )
RETURN
    IF (
        ISINSCOPE ( 'Date'[Year] ),
        _VALUE,
        MAX ( 'Table1'[Order Intake Actuals Cumulative] )
            + SUMX ( 'Table2', 'Table2'[Order Intake forecast (non cumulative)] )
    )

Order Intake (cumulative) =
IF (
    HASONEVALUE ( Table1[Order Intake Actuals Cumulative] ),
    MAX ( 'Table1'[Order Intake Actuals Cumulative] ),
    MAXX ( ALL ( 'Table1' ), 'Table1'[Order Intake Actuals Cumulative] )
        + SUMX (
            FILTER ( ALL ( 'Table2' ), 'Table2'[Date] <= MAX ( 'Date'[Date] ) ),
            'Table2'[Order Intake forecast (non cumulative)]
        )
)

Get the correct result:

I attach my sample below for your reference.

Best regards,

Community Support Team_yanjiang

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