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.

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 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
2 ACCEPTED SOLUTIONS
Community Support

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.

Community Support

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.

4 REPLIES 4
Helper IV

@v-yanjiang-msft Thank you!!

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

Community Support

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.

Community Support

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.

## Helpful resources

Announcements

#### New forum boards available in Real-Time Intelligence.

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

#### Power BI Monthly Update - May 2024

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

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors