topic Re: Power bi: Cumulative measure problems in DAX Commands and Tips

Power bi: Cumulative measure problems

Moji_mook — Tue, 05 Dec 2023 15:59:26 GMT

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.

Re: Power bi: Cumulative measure problems

some_bih — Tue, 05 Dec 2023 15:15:22 GMT

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

Re: Power bi: Cumulative measure problems

Moji_mook — Tue, 05 Dec 2023 15:56:05 GMT

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.

Re: Power bi: Cumulative measure problems

some_bih — Tue, 05 Dec 2023 20:42:15 GMT

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

Please share your table inputs and expected output.

Usually, YTD is fair cumulative measure.

Re: Power bi: Cumulative measure problems

Moji_mook — Tue, 05 Dec 2023 23:56:02 GMT

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]))))

Re: Power bi: Cumulative measure problems

some_bih — Wed, 06 Dec 2023 20:02:23 GMT

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

Re: Power bi: Cumulative measure problems

Anonymous — Fri, 22 Dec 2023 08:46:35 GMT

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.