cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
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
Ask Amanda Anything Q&A

Ask Amanda Anything Q&A

Learn the answers to some of the questions asked during the Amanda Triple A event.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

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: 242 members 2,806 guests
Please welcome our newest community members: