Power bi: Cumulative measure problems

[Moji_mook]

I try to do cumulative on a measure name #Exp this is my problem.

I have a table name Expenses which has no relationship with any table. 

This is my measure:

#Exp =

CALCULATE (

SUM( 'Expenses' [Expenses]),

FILTER(

'Expenses' ,

'Expenses' [Month] = SELECTEDVALUE('Date' [Month])

) &&

'Expenses ' [Poo]] = SELECTEDVALUE (tbMaster_UnitGroup[New_Map]) ||

ISFILTERED (tbMaster_UnitGroup[New_Map]) = FALSE()

&&

'Expenses' [EngagementProfitCtrName] = SELECTEDVALUE (Alocation Summary [UnitG]) ||

ISFILTERED (Alocation Summary[UnitG]) = FALSE()

&&

'Expenses [FY] = SELECTEDVALUE ('Date "[FY]) ||

ISFILTERED(Date' [FY]) = FALSE ()

&&

'Expenses' [month] = SELECTEDVALUE ('Date' [Month]))))

I try many way but this measure is the only way that returns my data correctly.

The problem I face is that I tried to get cumulative of #Exp and I did every way to cumulative by this measure, but it is still not cumulative. 

I have tired something like 

Cul = calculate( #Exp , Filter('expenses',expenses[Date] <=max( expenses[Date])) It is not working.

[Anonymous · Accepted solution]

Hi @Moji_mook ,

We can create a measure.

Cul_exp = SUMX(FILTER(ALLSELECTED('date'),YEAR('date'[Date])= YEAR(MAX('date'[Date]))&&MONTH('date'[Date])<=MONTH(MAX('date'[Date]))),[#Exp])

If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. Thank you.

Best Regards,

Neeko Tang

If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly. 

[some_bih]

Hi @Moji_mook 

Cummulative measure should be created using Time intelligence functions depending of your requirements

Check link https://learn.microsoft.com/en-us/dax/time-intelligence-functions-dax 

[Moji_mook]

Thanks you for the solution. Can you suggest me what time intelligence should I use for cumulative my measure. I have no Idea how to combine my measure with them 

I have tired something like 

Cul = calculate( #Exp , Filter('expenses',expenses[Date] <=max( expenses[Date])) It is not working.

[some_bih]

Hi @Moji_mook **bleep**.values should not be the issue.

Please share your table inputs and expected output.

Usually, YTD  is fair cumulative measure.

[Moji_mook]

My table look like this:

Month | #Exp  | Cul_Exp (expected results)

Jan         100       100

Feb         100       200

Mar         100      300

Apr          100      400

May         100       500

Jun           100       600

Jul             100       700

Aug          100        800

Sep            100       900

Oct             100      1000

Nov             100     1100

Dec             100       1200

[#Exp] display correctly but Cul_Exp (expected results) not working.

Month is from date table. [Exp] Is the measure I provided below. #Exp sum from [Expenses] in table expenses which has no relationship with any table. It cannot has relationship with any table because every time it has relationship with table it returns incorrect value.
#Exp =

CALCULATE (

SUM( 'Expenses' [Expenses]),

FILTER(

'Expenses' ,

'Expenses' [Month] = SELECTEDVALUE('Date' [Month])

) &&

'Expenses ' [Poo]] = SELECTEDVALUE (tbMaster_UnitGroup[New_Map]) ||

ISFILTERED (tbMaster_UnitGroup[New_Map]) = FALSE()

&&

'Expenses' [EngagementProfitCtrName] = SELECTEDVALUE (Alocation Summary [UnitG]) ||

ISFILTERED (Alocation Summary[UnitG]) = FALSE()

&&

'Expenses [FY] = SELECTEDVALUE ('Date "[FY]) ||

ISFILTERED(Date' [FY]) = FALSE ()

&&

'Expenses' [month] = SELECTEDVALUE ('Date' [Month]))))

[some_bih]

Hi @Moji_mook 

Create date table and conect it with your fact table. After that try YTD measure

Measure YTD=TOTALYTD(SUM( 'Expenses' [Expenses]),DateTime[DateKey] )

Link for details

https://learn.microsoft.com/en-us/dax/totalytd-function-dax?WT.mc_id=DP-MVP-4025372