cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Pixel New Member
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

1 REPLY 1
Community Support Team
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.

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

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

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

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

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.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

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

Users Online
Currently online: 328 members 3,269 guests
Please welcome our newest community members: