Calculate Transaction age for Vendor Aging Report

[deeksha]

Hi all,

I am a relatively new user in Power BI and am needing assistance with the following requirement.

My client is running on Dynamics 365 and would want to replicate The Vendor aging Report in Power BI and i am connecting to the client using OData.

I followed this link for reference https://community.powerbi.com/t5/Desktop/Accounts-Receivable-Aging-Report/td-p/106367

And i have created a measure which calculates the transactions age using the due date and by using this measure i am trying to calculate the amounts for 

-All  transactions which  fall under column 0-30Days

-All  transactions which  fall under column 31- 60Days

-All transactions in which fall under column 61- 90Days

-All transactions which fall over column +90 days

-Transaction for the current date

I am using this formula to create calculated columns for the respective periods 

Period 1-30 = IF(([No of days] > 0 && [No of days] <31), VendAgingGlobals[AmountMST], BLANK())

Period 31-60 = IF(([No of days] >30 && [No of days] <61), VendAgingGlobals[AmountMST], BLANK())

Period 61-90 = IF(([No of days] >60 && [No of days] <91), VendAgingGlobals[AmountMST], BLANK())

Period 90+ = IF(([No of days] >90), VendAgingGlobals[AmountMST], BLANK())

Period 0 or current = IF((VendAgingGlobals[No of days] = 0), VendAgingGlobals[AmountMST], BLANK())

And the current date should be the date which is selected from the slicer, like if i choose 3rd nov 2017 in slicer, it should be the currenr date and i should get the amounts according to that but the measure which i used to calculate the transactions age is not giving me the correct values as you can see in the picture, all the values are coming in 90+ period.

This is the measure which is used - 

No of days = DATEDIFF(VendAgingGlobals[DueDate],TODAY(),DAY)

Can anyobody help me how can i get the correct age of the transactions for the different required periods

[danextian]

Hi @deeksha,

You are using a calculated column which is computed during the database process and then stored in the model which will ony update upon dataset refresh or, of course,  when its formula or reference columns have been modified (more about calculated columns vs measures at https://www.sqlbi.com/articles/calculated-columns-and-measures-in-dax/). That being said, the age colum that has already been stored in the model will not update when you select anything in the slicer so use a measure instead. 

In the sample pbix, I created a disconnected (no relationship to fact) calculated table which will hold the age and the corresponding buckets. 

AgingBucket =
VAR __BASE =
    SELECTCOLUMNS (
        GENERATESERIES ( 0, DISTINCTCOUNT ( 'Fact'[Due Date] ), 1 ),
        "Age", [Value]
    )
RETURN
    ADDCOLUMNS (
        __BASE,
        "Bucket", SWITCH (
            TRUE (),
            [Age] > 91, "90+",
            [Age] > 60, "61 to 90",
            [Age] > 30, "31 to 60",
            [Age] > 0, "1  to 30",
            " Current"
        )
    )

I would then create a measure to for the invoice age and another one for the amount by age

Invoice Age =
DATEDIFF (
    SELECTEDVALUE ( 'Fact'[Due Date] ),
    CALCULATE ( MAX ( 'Fact'[Due Date] ), ALLSELECTED ( 'Fact' ) ),
    DAY
)

Invoice Amount by Age = 
CALCULATE (
    [Sum of Amount],
    FILTER (
        'Fact',
        COUNTROWS (
            FILTER (
                AgingBucket,
                [Invoice Age] >= AgingBucket[Age]
                    && [Invoice Age] <= AgingBucket[Age]  
            )
        ) > 0
    )
)

Since Aging Bucket columns and Invoice Age measure has no relationship, you will end up seeing a weird result if you put them together in a visual. Create another measure to fix this and use this in your visual instead.

InvoiceAge = 
IF ( NOT ( ISBLANK ( [Invoice Amount by Age] ) ), [Invoice Age] )

You may download the sample pbix here

https://drive.google.com/file/d/1U7QCOQafrAi9Qm3og0r2anStYe4KlKDk/view?usp=sharing 

[deeksha]

@danextianHey, i am getting the amounts separately in 0-30,30-60, 60-90 and 90+ period but the disconnected table which we created by using formula 

AgingBucket = VAR __BASE = SELECTCOLUMNS ( GENERATESERIES ( 0, DISTINCTCOUNT ( 'Fact'[Due Date] ), 1 ), "Age", [Value] ) RETURN ADDCOLUMNS ( __BASE, "Bucket", SWITCH ( TRUE (), [Age] > 91, "90+", [Age] > 60, "61 to 90", [Age] > 30, "31 to 60", [Age] > 0, "1 to 30", " Current" ) )

is calculating the right values only for the transdates which are available, i mean if i choose 13th sep 2017 in slicer, but there is no transaction on 13th sep, so the DISTINCTCOUNT function will choose the nearest date like 2nd august 2017 and will assign 1 to it, which make it to fall in 1-30 period but it should have come in 30-60, with an age of 41 days, how can i get that???

@amitchandak @vivran22 

[vivran22]

Hello @deeksha,

I have tried to recreate the scenario. I am not sure if this is what you are looking for.

You may find the solution pbix file here

Regards,
Vivek

If it helps, please mark it as a solution

Kudos would be a cherry on the top 🙂

https://www.vivran.in/

[deeksha]

@vivran22Hey Thank you for the reply, but your sample report is working and evaluating the data in other way, its not how i want in my report,

here you can check my sample report data

https://1drv.ms/u/s!Aj76OGyQ6tJOrgQcykllXW54-nfy?e=ALyEc3

[amitchandak]

Can you share sample data and sample output. If possible please share a sample pbix file after removing sensitive information.Thanks.
Proud to be a Datanaut My Recent Blog -
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

[deeksha]

@amitchandak  here is my sample report data

https://1drv.ms/u/s!Aj76OGyQ6tJOrgQcykllXW54-nfy?e=ALyEc3

[deeksha]

@danextian Thank you so much for your valuable answer, i have understood and created the disconnected table and measures according to you, but i need the sum of amount for 30 days in a seprate column and similarly for 60, 90 and 90+, so should i create separate measures for all like the invoice age? but for that do i have to change my bucket table too?

[danextian]

Hi @

You can try the formula below to compute for the invoice amount per bucket.

Invoice Amount - 0 to 30 days = 
CALCULATE (
    [Sum of Amount],
    FILTER ( 'Fact', [Invoice Age] >= 0 && [Invoice Age] <= 30 )
)

[deeksha]

@danextian Hi,

Its not working, i have created 3 more like the invoice amount for 0-30,

Invoice Amount - 30 to 60 days =

CALCULATE (

    [Sum of Amount],

    FILTER ( 'VendAgingGlobals', [Invoice Age] >= 31 && [Invoice Age] <= 60 )

)

Invoice Amount - 60 to 90 days =

CALCULATE (

    [Sum of Amount],

    FILTER ( 'VendAgingGlobals', [Invoice Age] >= 61 && [Invoice Age] <= 90 )

)

Invoice Amount - 90+ days =

CALCULATE (

    [Sum of Amount],

    FILTER ( 'VendAgingGlobals', [Invoice Age] >= 90 )

)

But its evaluating wrong amounts.

What i need in my report is to calculate the correct age from the date selected in the slicer.

Here is my sample report data

https://1drv.ms/u/s!Aj76OGyQ6tJOrgQcykllXW54-nfy?e=ALyEc3 

[amitchandak]

To have datediff as measure you need yo have a level, So you need to use values or summarize

datediff =
var _max = maxx(allselected(Date),Date[Date])
return					
calculate(average(No of days = DATEDIFF(VendAgingGlobals[DueDate],_max,DAY)),values(VendAgingGlobals[OrderId]))
//or
averagex(values(VendAgingGlobals[OrderId]),DATEDIFF(VendAgingGlobals[DueDate],_max,DAY))					

Put an IF on top of it to get values.

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution.
In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blogs -Decoding Direct Query - Time Intelligence, Winner Coloring on MAP, HR Analytics, Power BI Working with Non-Standard TimeAnd Comparing Data Across Date Ranges
Connect on Linkedin

[deeksha]

@amitchandakThanks for the reply, but i don't have order id in my table, i only have accountNum, Voucher and lastsettleVoucher and in 

var _max = maxx(allselected(Date),Date[Date])

which date i have to use?

I have due date, transdate and lastsettledate in my table.