DAX Formula

[Anonymous]

Hi all

I have a DAX challenge that i haven't been able to solve.

I have 3 related tables;

GL Account (columns: No, Name, type)
GL Entries (Columns: Date, GL account no, Amount)
Calender (columns: Date, year, financial year)

I wish to make a measure (only 1) Which calculates sum(GL Entries[Amount]) when the Account type in GL Account table has value "Income" and when GL Account table has value "Balance" the calculation should be like 
CALCULATE(Sum(GL Entries[Amount]),FILTER(ALL(Calender[Date]),DimDate[Date] <= Max(Calender[Date])))

This would enable me to return a pivottable to excel with accounts as rows and 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).

Any suggestions?

Br Michael

[v-juanli-msft]

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