Creating an Aging Schedule while using a Direct SQL Link

[gusdahu]

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

[v-jiascu-msft]

Hi @gusdahu,

Could you please mark the proper answers as solutions?

Best Regards,

Dale

[v-jiascu-msft]

Hi @gusdahu,

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

Best Regards,

Dale

[gusdahu]

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

[v-jiascu-msft]

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" )
    )

Best Regards,

Dale

[gusdahu]

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

[v-jiascu-msft]

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

[gusdahu]

Hi v-jiascu-msft,

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

Regards,

Gus Dahu

[v-jiascu-msft]

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

Best Regards,
Dale