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

Creating an Aging Schedule while using a Direct SQL Link

Power BI Community,

 

Currently, I have data feeding into Power BI from PeopleSoft via Direct Query. How can I create an aging schedule of open Purchase Orders using the PO Date?

 

Regards,

Gus Dahu

8 REPLIES 8
Community Support Team
Community Support Team

Re: Creating an Aging Schedule while using a Direct SQL Link

Hi @gusdahu,

 

What's Aging Schedule exactly? Can you share a dummy sample that will show up the data structure?

 

Best Regards,

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
gusdahu Member
Member

Re: Creating an Aging Schedule while using a Direct SQL Link

Hi v-jiascu-sft,

 

In my situation, an aging schedule will classify Open Purchase Orders (POs) into buckets to reflect the number of days the PO has been outstanding. The buckets will be something like 0-30 days, 31-60 days, 90 plus days, etc. Currently the data set population has the following fields:

 

  • Unique Identifier; and
  • PO Date.

How would I be able to classify POs based off PO date into the buckets mentioned above? Keep in mind that I am using Direct Dequel and thus will not be able to use the "Edit Query" function or conditional formatting.

 

Regards,

Gus Dahu

Community Support Team
Community Support Team

Re: Creating an Aging Schedule while using a Direct SQL Link

Hi Gus Dahu,

 

Firstly, I would make it clear that we can use Direct Query and Import together now since the preview of power-bi/desktop-composite-models

Now, everything seems easy. If you want to filter the buckets, you need to create a new table. If you just want to give every PO a label, please try a measure like below.

Measure =
VAR temp =
    DATEDIFF ( MIN ( [HIRE_DATE] ), TODAY (), DAY )
RETURN
    IF (
        temp >= 0
            && temp <= 30,
        "0-30",
        IF ( temp >= 31 && temp <= 60, "31-60", ">=61" )
    )

Creating_an_Aging_Schedule_while_using_a_Direct_SQL_Link

 

Best Regards,

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Community Support Team
Community Support Team

Re: Creating an Aging Schedule while using a Direct SQL Link

Hi @gusdahu,

 

Could you please mark the proper answers as solutions?

 

Best Regards,

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
gusdahu Member
Member

Re: Creating an Aging Schedule while using a Direct SQL Link

Hi,

 

I'm getting stuck on the second part of your DAX formula. For some reason my dax formula bar is not auto populating when I begin to type the "return if" statement. Any suggestions?

 

Regards,

Gus Dahu

Community Support Team
Community Support Team

Re: Creating an Aging Schedule while using a Direct SQL Link

Hi @gusdahu,

 

I guess you missed the ")" in the formula. We should close every part of the formula to keep it complete. Please check it out.

 

 

Best Regards,
Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
gusdahu Member
Member

Re: Creating an Aging Schedule while using a Direct SQL Link

Hi v-jiascu-msft,

 

I added the ")" and unfortunately it did not work. Any other suggestions?

 

Regards,

Gus Dahu

 

Community Support Team
Community Support Team

Re: Creating an Aging Schedule while using a Direct SQL Link

Can you share some snapshots or the file? @gusdahu,

 

 

Best Regards,
Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.