cancel
Showing results for
Did you mean:
Highlighted
New Member

## DAX Formula

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

Community Support Team

## Re: DAX Formula

Hi @Pixel

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.

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

Announcements

#### Community Highlights

Find out what's new in the Power BI Community!

#### Power Platform Summit North America

Register by September 5 to save \$200

#### Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

#### Watch Sessions On Demand!

Continue your learning in our online communities.

Top Kudoed Authors
Users Online
Currently online: 321 members 2,833 guests
Recent signins: