Forum Discussion

jalaomar's avatar
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 reciev...
Need Help
  • v-yanjiang-msft's avatar
    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.

     

  • v-yanjiang-msft's avatar
    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.