Exception aggregation

[dp32]

Hi,

I have a Pivot Table with a P&L account hierarchy. For some of the accounts, I need an exceptional aggregation other than sum.
E.g. for the account Depreciation Tangibles, the MTD values should not sum up to a QTD and YTD total, but just show the value of the last month of the quarter (or year).

How can I do this in Power BI?

Thanks in advance!

[Anonymous]

Hi @dp32,

Perhaps you can take a look at the following blog about the hierarchy level, then you can use if statement with filter check current hierarchy level and replace it will specific formula.

Clever Hierarchy Handling in DAX 
Regards,

Xiaoxin Sheng

[amitchandak]

If you have date table you can do like

Cumm Sales = CALCULATE(SUM(Sales[Sales Amount]),filter(date,date[date] =maxx(date,date[date])))

Or

Cumm Sales = CALCULATE(SUM(Sales[Sales Amount]),filter(date,date[date] =maxx(date,endofmonth(dateadd(date[date]),-1,month))))

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

[dp32]

Thanks, but this will not result in an exception aggregation. It will then be applied to all accounts of the P&L hierarchy.

[vivran22]

Hello @dp32 

You can create measures using time intelligence functions like LASTDATE, CLOSINGBALANCEMONTH, CLOSINGBALANCEYEAR, CLOSINGBALANCEQUARTER, 

For this you would also need to create a calendar table.

Cheers!
Vivek

If it helps, please mark it as a solution
Kudos would be a cherry on the top 🙂

https://www.vivran.in/

Connect on LinkedIn