Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
pamsardinha
Frequent Visitor

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.

1 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:

YearMonthSaldoEPMSaldoEOMSaldoChangeSystem
2023Jan01010A
2023Feb102010A
2023Mar2012-8A
2023Apr12153A
2023May15205A
2023Jun205030A
2023Jan06565O
2023Feb6542-23O
2023Mar429553O
2023Apr9556-39O
2023May5652-4O
2023Jun 5050O

 

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! 

View solution in original post

4 REPLIES 4
pamsardinha
Frequent Visitor

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.

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
Super User
Super User

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.
NaveenGandhi_0-1685717100117.png
 
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:

YearMonthSaldoEPMSaldoEOMSaldoChangeSystem
2023Jan01010A
2023Feb102010A
2023Mar2012-8A
2023Apr12153A
2023May15205A
2023Jun205030A
2023Jan06565O
2023Feb6542-23O
2023Mar429553O
2023Apr9556-39O
2023May5652-4O
2023Jun 5050O

 

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! 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.