Forum Discussion
Calculated measure does not work with data hierarchy
Hello,
I am trying to calculate the saldo absolute change between months, therefore created two measures:
1. this one gets the saldo by the end of month:
SaldoEOM =
VAR Latest_Date = MAX('Table'[Date])
RETURN
CALCULATE (SUM('Table'[Saldo]), 'Table'[Date] = Latest_Date)
2. this one gets the saldo by the end of previous month:
Saldo End of Previous Month =
VAR Endmonth = EOMONTH(max('Table'[Date]), -1)
RETURN
CALCULATE(
[SaldoEOM],
'Table'[Date] = Endmonth
)
The issue is when I try to use in the a table with date hierarchy (only Year and Month), the 'Saldo End of Previous Month' shows nothing, areas when I use the whole date, I can see the result.
The first one works perfectly fine, so the problem is not with the date format/hiearchy.
I tried several different options to calculate this second part, but without success. Any better idea?
Thank you.
Hi, once again thank you for the reply.
I was able to work around by using the following dax syntax, just adding "ALLEXCEPT" at the end.
Now it works with the date hierarchy Year, Month.
By using ALLEXCEPT I removed all filters from Table1, except the ones I needed to get the right result 'Table1'[Saldo], 'Table1'[Date], 'Table2'[System], 'Table3'[variable1], 'Table4'[variable1]*I changed the name of the measure to be more concise
SaldoEPM = CALCULATE(SUM('Table1'[Saldo]), 'Table1'[Date] = EOMONTH(max('Table1'[Date]),-1), ALLEXCEPT('Table1', 'Table1'[Saldo], 'Table1'[Date], 'Table2'[System], 'Table3'[variable1],'Table4'[variable1]))The result now looks something like this:
Year Month SaldoEPM SaldoEOM SaldoChange System 2023 Jan 0 10 10 A 2023 Feb 10 20 10 A 2023 Mar 20 12 -8 A 2023 Apr 12 15 3 A 2023 May 15 20 5 A 2023 Jun 20 50 30 A 2023 Jan 0 65 65 O 2023 Feb 65 42 -23 O 2023 Mar 42 95 53 O 2023 Apr 95 56 -39 O 2023 May 56 52 -4 O 2023 Jun 50 50 O I only have the issue now that the SALDOEPM to calculate the differece from june of system O, is not recognizing the value from May. But I will open another ticket to this issue, since the issue here was working with date hierarchy and now it works!
4 Replies
Thank you for your reply NaveenGandhi !
Unfortunetely I am working with powerbi dataset and I cannot create tables since I have no rights to do it.
NaveenGandhiMemorable Member
pamsardinha
If thats the case, Use a measure like below.Previous_amt =
CALCULATE (
SUM ( 'Previous Indemnity'[Amount] ),
ALL ( 'Previous Indemnity' ),
PREVIOUSMONTH ( 'Previous Indemnity'[Transaction Date] )
)
If you still have issues, Share sample data or PBIX.If this post helps, then please consider Accept it as the solution to help the others find it more quickly. Appreciate your kudos!!
- NaveenGandhi
Hello pamsardinha
Try Dax like this.Previous_amt = CALCULATE(sum('Amount),PREVIOUSMONTH('Calendar'[Date]))
Create a calendar table with a relationship between your table and calendar. Use the calendar[month]/Year in slicer, table hierarchy and measure above. You should be able to acheive something as below.Let me know if this helps or if you have any question.If this post helps, then please consider Accept it as the solution to help the others find it more quickly. Appreciate your kudos!!Hi, once again thank you for the reply.
I was able to work around by using the following dax syntax, just adding "ALLEXCEPT" at the end.
Now it works with the date hierarchy Year, Month.
By using ALLEXCEPT I removed all filters from Table1, except the ones I needed to get the right result 'Table1'[Saldo], 'Table1'[Date], 'Table2'[System], 'Table3'[variable1], 'Table4'[variable1]*I changed the name of the measure to be more concise
SaldoEPM = CALCULATE(SUM('Table1'[Saldo]), 'Table1'[Date] = EOMONTH(max('Table1'[Date]),-1), ALLEXCEPT('Table1', 'Table1'[Saldo], 'Table1'[Date], 'Table2'[System], 'Table3'[variable1],'Table4'[variable1]))The result now looks something like this:
Year Month SaldoEPM SaldoEOM SaldoChange System 2023 Jan 0 10 10 A 2023 Feb 10 20 10 A 2023 Mar 20 12 -8 A 2023 Apr 12 15 3 A 2023 May 15 20 5 A 2023 Jun 20 50 30 A 2023 Jan 0 65 65 O 2023 Feb 65 42 -23 O 2023 Mar 42 95 53 O 2023 Apr 95 56 -39 O 2023 May 56 52 -4 O 2023 Jun 50 50 O I only have the issue now that the SALDOEPM to calculate the differece from june of system O, is not recognizing the value from May. But I will open another ticket to this issue, since the issue here was working with date hierarchy and now it works!