DAX Formula

Hi Anonymous

It is impossible to return a pivottable to excel from Power BI Desktop which pivot table is not static.

If you want to perform the slicer and the pivot table work together, please write measures directly in Excel->Power Pivot.

You could create such a matrix visual within Power BI Desktop, and make it change with the date slicer.

However before start, please let me know exactly about your requirement.

Which is the "DimDate[Date]"?

Which "date" column do you want to add in the slicer to filter the sum values? from "Calendar table"?

"the amount on income accounts will be filtered by a date filter interval (eg may 2018) and the amount on balance accounts will always the the total balance amount per max filter date (eg. 31st of may 2018)"

the amount on income accounts should be the total sum before and on the filtered date (eg may 2018) 

such as (sum amount between 2018/1/1~2018/5/31)

the amount on balance accounts should be the amount of the max date of the month selected (eg. 31st of may 2018)

such as (dum amount at 2018/5/31)

Please correct my understanding if i'm not right.

Best Regards

maggie