Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
Solved! Go to Solution.
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!
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.
@pamsardinha
If thats the case, Use a measure like below.
Hello @pamsardinha
Try Dax like this.
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!
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
118 | |
101 | |
71 | |
61 |