cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
thampton Member
Member

Great Plains & Power BI - HATB Report

Wondering if anyone has experience recreating standard reports from GP in Power BI...

 

The Historical Aged Trial Balance SSRS report uses a stored procedure and temp tables within GP, but i would like to recreate in Power Bi. Below is a table similar to what i currently have: 

 

Document #         Invoice Date               Payment Date               Aging                   Amount

1                           1/1/2019                    1/10/2019                     9                           $15

2                           1/1/2019                    1/20/2019                     19                         $20

3                           1/1/2019                    2/2/2019                       31                         $25

4                           1/1/2019                                                          158                       $30

 

I would like to be able to choose an "As-Of" Date for the aging. For example, if i default it to show current day, my aging would be $30. Everything has been paid but invoice 4. However, if i wanted to see my aging as of 1/31/2019 it would $55 because invoice 3 & 4 had not been paid yet. 

 

How could i write this dax formula? For this example lets say i want my slicer to show month end dates: 1/31/2019, 2/28/2019,etc.....

 

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Great Plains & Power BI - HATB Report

Hi @thampton ,

 

Create a calendar table not related with the other table then add the following measure:

Open invoices =
VAR Selected_date =
    MAX ( 'Calendar'[Date] )
RETURN
    CALCULATE (
        SUM ( Sales[Amount] );
        FILTER (
            ALL ( Sales[Invoice Date]; Sales[Payment Date] );
            (
                Sales[Payment Date] = BLANK ()
                    || Sales[Payment Date] >= Selected_date
            )
                && Sales[Invoice Date] <= Selected_date
        )
    ) + 0

As you can see below is calculating the correct amounts, I added an invoice with creation and payment in previous periods for you to check that is also needed to take into accoutn the invoice date for the calculation.

 

Invoice_open.gif

 

Regards,

MFelix



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




View solution in original post

1 REPLY 1
Super User
Super User

Re: Great Plains & Power BI - HATB Report

Hi @thampton ,

 

Create a calendar table not related with the other table then add the following measure:

Open invoices =
VAR Selected_date =
    MAX ( 'Calendar'[Date] )
RETURN
    CALCULATE (
        SUM ( Sales[Amount] );
        FILTER (
            ALL ( Sales[Invoice Date]; Sales[Payment Date] );
            (
                Sales[Payment Date] = BLANK ()
                    || Sales[Payment Date] >= Selected_date
            )
                && Sales[Invoice Date] <= Selected_date
        )
    ) + 0

As you can see below is calculating the correct amounts, I added an invoice with creation and payment in previous periods for you to check that is also needed to take into accoutn the invoice date for the calculation.

 

Invoice_open.gif

 

Regards,

MFelix



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




View solution in original post

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.

New Badges

Incoming: New and Improved Badges

Exciting news: We've given our badges an overhaul and added brand news ones.

Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 139 members 1,742 guests
Please welcome our newest community members: