Cumulative function

[tahar1407]

Hi all,

I'm trying to reproduce this Excel report : 

with those formula

For :

Estimated Freight

Add on 

Estimated Duty

Overhead           

Indirect Manufacturing 

Royalties

Payables & Purchases

Orders and Receivables

I added them in Power BI as measures.

The Net Balance is a Cumulative of all those Measures + $906590 

Total measure 1 = [Payables and Purchases 2] + [Orders and Receivables] + sum('Calendar'[Add on]) + sum('Calendar'[Indirect Manufacturing]) + SUM('Calendar'[Overhead]) + SUM('Calendar'[Royalties]) + [Estimated Duty] + [Estimated Duty] - 906590

and this is the cumulative measure :

but values are not the same between my Excel sheet and PowerBI:

maybe my formulas are wrong. I'm working on it since 2 days ago and I'm blocked. 

Any help, please 

@v-shex-msft 

[v-shex-msft]

Hi @tahar1407,

According to your description, it sounds like a common cumulative requirement across multiple fields.
If that is the case, I'd like to suggest you invoke an iterator function to looping on your tables.

Measure =
VAR currDate =
    MAX ( Calendar[Date] )
RETURN
    SUMX (
        FILTER ( ALLSELECTED ( 'Fact' ), 'Fact'[Date] <= currDate ),
        [column1] + [column2] + [Meaure1] + [Meaure1]
    )

Regards,

Xiaoxin Sheng

[tahar1407]

Hi,

I need to start my cumulative total on a specific date/week
This is what I have now :

Cumulative Total Measure =
CALCULATE([Total measure 1],

FILTER(ALL('Calendar'),'Calendar'[Week 3] <= MAX('Calendar'[Week 3]) && 'Calendar'[Week 3] > DATE(2021,07,17)))

[Total measure 1] : is the total what all measures

as you can see, Total measure 1 it start with the date that I want but the cumulative start one week after

[tahar1407]

I have this Cumulative Measure :

Cumulative Total Measure = CALCULATE([Total measure 1],

FILTER(ALL('Calendar'),'Calendar'[Week 3] <= MAX('Calendar'[Week 3]) && 'Calendar'[Week 3] >= DATE(2021,07,17))

)

It works but at a moment it stopped working properly :

 as you can see, the Total Measure 1 is the total of all columns before 

the sum is correct but not the cumulative it should be 

($9,408,469)

the difference is 1445917 is the sum of 1250000 + 195917

@v-shex-msft 

@v-shex-msft 

[v-shex-msft]

Hi @tahar1407,

Any specific calculation for loop in raw measure expressions that you invoke? AFAIK, you can't directly apply multiple aggerations to measure expression or they will show the wrong reuslts. 

For this scenario, you can try to create a variable table with summarize function to apply the first aggregation and then use the iterator function to apply the second one.

Measure Totals, The Final Word 

Regards,
Xiaoxin Sheng