Calculate Percentage Change from Present Day to Previous Day

[JCBI1023]

To whom that can help,

Below is a snip of my data:

I want to calculate the percetange change as well as  the numerical +/- value for the Current Due, 31-60, 61-90, Over 90 & Balance Due column for each customer (cust. no) from today's date to the previous date. 

Please let me know what other information you need.

[v-haibl-msft]

@JCBI1023

I’m not sure how do you want to calculate the percentage change. Only Current Due column has numerical values in your table. So I used this column to do the calculation. I use the current day due subtract the previous day due, and divide previous day due. Maybe the calculation is not completely same as you desired, but should be similar.

Percent = 
VAR CurrentDate = Table1[Date]
VAR PreDate =
    CALCULATE (
        MAX ( Table1[Date] ),
        FILTER ( Table1, Table1[Date] < CurrentDate )
    )
VAR PreDue =
    LOOKUPVALUE (
        Table1[Current Due],
        Table1[Date], PreDate,
        Table1[Cust No], Table1[Cust No]
    )
RETURN
    (
        IF ( PreDue <> BLANK (), ( Table1[Current Due] - PreDue )
            / PreDue
    )
)

31 - 60 = 
IF (
    ABS ( Table1[Percent] ) > 0.31
        && ABS ( Table1[Percent] ) < 0.6,
    ABS ( Table1[Percent] )
)

Best Regards,

Herbert

[JCBI1023]

@v-haibl-msft

Thanks again. I just attempted to model my data and add the new "Percentage" column. I am getting the following error:

[Sean]

@JCBI1023 check what DATA Type your Sheet1[Date] column is? it looks like it is text instead of date?

For a quick visual reference date/numbers will always be right-justified in the column while text will be left-justified

Change the Data Type in the Modeling tab

[JCBI1023]

@Sean That was correct! I have changed it to Date. Now I am getting this message.

[v-haibl-msft]

@JCBI1023

According to the warning message, it seems there are multiple Branches have the same Cust No and Date. You can try to update the column formula as below to restrict Branch.

Percent = 
VAR CurrentDate = Table1[Date]
VAR PreDate =
    CALCULATE (
        MAX ( Table1[Date] ),
        FILTER ( Table1, Table1[Date] < CurrentDate )
    )
VAR PreDue =
    LOOKUPVALUE (
        Table1[Current Due],
        Table1[Date], PreDate,
        Table1[Cust No], Table1[Cust No],
        Table1[Branch], Table1[Branch]
    )
RETURN
    (
        IF ( PreDue <> BLANK (), ( Table1[Current Due] - PreDue ) / PreDue )
    )

Best Regards,

Herbert

[JCBI1023]

@v-haibl-msft - You're exactly right. One customer can have balances in different branches, but totaling will be acceptable, as they collect per customer, not branch.

 I will attempt this tomorrow. Thank you very much,

[Sean]

@JCBI1023 I agree with @v-haibl-msft

Here's my response from last week regarding the same exact error type

http://community.powerbi.com/t5/Desktop/Lookupvalue-will-not-return-time-value/m-p/78266/highlight/f...

The error you are getting indicates that the LOOKUPVALUE function encounters multiple possible answers in your data set

when its looking to return only one!

[JCBI1023]

@v-haibl-msft Thank you for the %. The second part of my question was how to calcute the numerical change from the previous day to the current day. 

For example, if the Current Due for Customer X on Oct 19th was $500 and today it is $100... I need that number.

To give you some background, I am using the Power BI in accordance with my Office365 subscription. It is the free version. 

Each day at 5PM I run an AR Aging Report against our Business System and it gives me the current aging. Then I populate my source spreadsheet with the information and date each line. I then refresh my PowerBI report and update the Report Level filter to the current date, The next morning the Managers can look at the published report and the numbers will be current as of 5PM the previous day. That's working well. Now they want to see the change between current day and previous day. 

Source Data in ExcelReport Level Date Filter

[v-haibl-msft]

@JCBI1023

Just try with following column expression, it should be almost same as the percent column expression.

Numerical Change =
VAR CurrentDate = Table1[Date]
VAR PreDate =
    CALCULATE (
        MAX ( Table1[Date] ),
        FILTER ( Table1, Table1[Date] < CurrentDate )
    )
VAR PreDue =
    LOOKUPVALUE (
        Table1[Current Due],
        Table1[Date], PreDate,
        Table1[Cust No], Table1[Cust No],
        Table1[Branch], Table1[Branch]
    )
RETURN
    (
        IF ( PreDue <> BLANK (), Table1[Current Due] - PreDue )
    )

Best Regards,

Herbert

[JCBI1023]

@v-haibl-msft Thank you so much for this. One final change, I promise. Then I will make this as solved. 

The formula to find the numerical change in value for the previous day worked great. Now, what if I wanted to find the the change for the week? In other words, take the current day's value and subtract it from what the value was 7 days ago. 

Thank you again. 

[v-haibl-msft · Accepted solution]

@JCBI1023

We should only need to update the PreDate to the date of 7 days ago.

Change_7 = 
VAR CurrentDate = Table1[Date]
VAR PreDate = CurrentDate - 7
VAR PreDue =
    LOOKUPVALUE (
        Table1[Current Due],
        Table1[Date], PreDate,
        Table1[Cust No], Table1[Cust No],
        Table1[Branch], Table1[Branch]
    )
RETURN
    (
        IF ( PreDue <> BLANK (), Table1[Current Due] - PreDue )
    )

Best Regards,

Herbert

[JCBI1023]

This is wonderful! Management wants one more thing and I can't figure out how to formulate the number.

You have helped me find the change from the current due to the current due 7 days ago. Now I simpy need the current due number from 7 days ago.  So I will have Current, Previous (from 7 days ago), Change....

[JCBI1023]

@v-haibl-msft

I think I may have answered my own question, would it just be:

Previous Current Due =
VAR CurrentDate = Sheet1[Date]
VAR PreDate = CurrentDate - 7
VAR PreDue =
LOOKUPVALUE (
Sheet1[Current Due],
Sheet1[Date], PreDate,
Sheet1[Cust No ], Sheet1[Cust No ],
Sheet1[Branch], Sheet1[Branch]
)
RETURN
(
IF ( PreDue <> BLANK (), PreDue )
)

[JCBI1023]

@v-haibl-msft @Herbert_Liu

Hello, when calculating the previous day, using this login : VAR PreDate = CurrentDate - 1 ...

61-90 Previous Day =
VAR CurrentDate = Sheet1[Date]
VAR PreDate = CurrentDate - 1
VAR PreDue =
LOOKUPVALUE (
Sheet1[61 - 90],
Sheet1[Date], PreDate,
Sheet1[Cust No ], Sheet1[Cust No ],
Sheet1[Branch], Sheet1[Branch]
)
RETURN
(
IF ( PreDue <> BLANK (), PreDue )
)

Since I do not update this on Saturday or Sunday, when Monday comes around there is no previous day, it is blank. How do I tell the formula to skip Saturday or Sunday, or if there isn't a "Previous day" then look for the value that is the previous regardless of how many days away it was. 

[JCBI1023]

Thank you for your reply. From the snippet, you are correct, onlt the Current Due column has numerical values. However,  farther down in the dataset, values do exist for the other columns. Let me give this a shot and get back to you. 

[BhaveshPatel]

Hi There,

I have used the PowerQuery to find the solution of your problem. However, this is not the one & only apporach. You can also use SWITCH or IF function in DAX to find the answer. I demonstrated to you in this example what powerquery can do in PowerBI.

Please follow the screenshots to calculate the right answer. Add a Custom Column for Today's Date & Changed the data type to DateAdd second custom column to find the difference between Today and due dateAdd conditional column with the logic shown in the screenshotPivot column as shown hereReplace null values in all the three newly created columns with 0final results.

You can also use powerquery to count the percentage change. 

[JCBI1023]

Many thanks for your detailed answer. I will attempt to give this a shot today.