Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
jalaomar
Helper IV
Helper IV

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 CumulativeTable 2 Order Intake forecast (non cumulative)
2023Jan10 
2023Feb20 
2023Mar60 
2023Apr150 
2023Maj200 
2023Jun300 
2023Jul 100
2023Aug 200
2023Sep 350
2023Okt 400
2023Nov 560
2023Dec 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 
2 ACCEPTED SOLUTIONS
v-yanjiang-msft
Community Support
Community Support

Hi @jalaomar ,

According to your description, here's my solution.

Sample:

Table1:

vyanjiangmsft_0-1690946436061.png

Table2:

vyanjiangmsft_1-1690946457214.png

Date table:

vyanjiangmsft_2-1690946484064.png

Relationship:

vyanjiangmsft_3-1690946505053.png

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:

vyanjiangmsft_4-1690946644231.png

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.

 

View solution in original post

Hi @jalaomar ,

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

vyanjiangmsft_0-1691030187722.png

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:

vyanjiangmsft_1-1691030349852.png

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.

View solution in original post

4 REPLIES 4
jalaomar
Helper IV
Helper IV

@v-yanjiang-msft Thank you!! 

jalaomar
Helper IV
Helper IV

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

Hi @jalaomar ,

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

vyanjiangmsft_0-1691030187722.png

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:

vyanjiangmsft_1-1691030349852.png

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
Community Support
Community Support

Hi @jalaomar ,

According to your description, here's my solution.

Sample:

Table1:

vyanjiangmsft_0-1690946436061.png

Table2:

vyanjiangmsft_1-1690946457214.png

Date table:

vyanjiangmsft_2-1690946484064.png

Relationship:

vyanjiangmsft_3-1690946505053.png

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:

vyanjiangmsft_4-1690946644231.png

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.

 

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.