Forum Discussion
Power bi: Cumulative measure problems
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.
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.
6 Replies
- some_bih
Community Champion
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
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.
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.
