Calculated measure does not work with data hierarchy

[pamsardinha]

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.

[pamsardinha]

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.

[NaveenGandhi]

@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!!

[pamsardinha · Accepted 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!