cancel
Showing results for
Did you mean:
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

Highlighted
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

#### Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

#### Community News & Announcements

Get your latest community news and announcements.

#### Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

#### Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Top Kudoed Authors
Users Online
Currently online: 285 members 2,590 guests
Recent signins: