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 CumulativeTable 2 Order Intake forecast (non cumulative)2023Jan10 2023Feb20 2023Mar60 2023Apr150 2023Maj200 2023Jun300 2023Jul 1002023Aug 2002023Sep 3502023Okt 4002023Nov 5602023Dec 530 Output Non-cumulative Order Intake MoM Year Month Order Intake (non-comulative) 2023Jan10 2023Feb10 2023Mar40 2023Apr90 2023Maj50 2023Jun100 2023Jul100 2023Aug200 2023Sep350 2023Okt400 2023Nov560 2023Dec530 Total 2440 Output cumulative Order Intake MoM Year Month Order Intake (cumulative) 2023Jan10 2023Feb20 2023Mar60 2023Apr150 2023Maj200 2023Jun300 2023Jul400 2023Aug600 2023Sep950 2023Okt1350 2023Nov1910 2023Dec2440

Forum Discussion

jalaomar

Helper IV

3 years ago

Solved

Cumulative Totals MoM

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

Need Help

v-yanjiang-msft

3 years ago

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.

Cumulative Totals MoM.pbix25 KB

v-yanjiang-msft

3 years ago

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.

Cumulative Totals MoM.pbix26 KB

4 Replies

v-yanjiang-msft

Community Support

3 years ago

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.

Cumulative Totals MoM.pbix25 KB

jalaomar

Helper IV

3 years ago

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

Community Support

3 years ago

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.

Cumulative Totals MoM.pbix26 KB

jalaomar
Helper IV
3 years ago
v-yanjiang-msft Thank you!!

Forum Discussion

Cumulative Totals MoM

4 Replies

Related content

Cumulative Total

MoM calculation

Cumulative Total

SumX? - cumulative running total

Cumulative Total