Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
gusdahu
Helper IV
Helper IV

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
v-jiascu-msft
Employee
Employee

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.
v-jiascu-msft
Employee
Employee

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.

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

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.

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

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.

Hi v-jiascu-msft,

 

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

 

Regards,

Gus Dahu

 

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.