Problems with getting previous day and latest day values

[Anonymous]

Hi, so I am working on a dashboard for internal stakeholders. 

It is a financial dashboard and i am stuck on one problem. 

What i want my outcome to look like is the one below. Where i can display the latest value by date available in the data and compare the value to previous day. then Subtract two values to get the "value" for payments made. : 

ALL the figures below are "Total overdue amounts" for each customer. 

in my case Total Overdue is a measure. 

The measure is dervied from adding 5 other measures. which gives the latest overdue till date. 

Could someone please help! 

[Jihwan_Kim]

Hi, @Anonymous 

Please check the below picture and the sample pbix file's link down below.

Payments Measure =
VAR currentcustomer =
MAX ( Data[Customer] )
VAR daterank =
RANKX (
FILTER ( ALL ( Data ), Data[Customer] = currentcustomer ),
CALCULATE ( MAX ( Data[Date] ) ),
,
DESC
)
VAR currentvalue =
CALCULATE (
SUM ( Data[Value] ),
FILTER (
ALL ( Data ),
Data[Customer] = currentcustomer
&& RANKX (
FILTER ( ALL ( Data ), Data[Customer] = currentcustomer ),
CALCULATE ( MAX ( Data[Date] ) ),
,
DESC
) = daterank
)
)
VAR previousvalue =
CALCULATE (
SUM ( Data[Value] ),
FILTER (
ALL ( Data ),
Data[Customer] = currentcustomer
&& RANKX (
FILTER ( ALL ( Data ), Data[Customer] = currentcustomer ),
CALCULATE ( MAX ( Data[Date] ) ),
,
DESC
) = daterank + 1
)
)
RETURN
IF ( ISFILTERED ( Data[Customer] ), currentvalue - previousvalue )

https://www.dropbox.com/s/qzx3wc2kniq14oy/gumdrop.pbix?dl=0 

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Linkedin: linkedin.com/in/jihwankim1975/

Twitter: twitter.com/Jihwan_JHKIM

[Anonymous]

Thank you Kim, I'm definitely getting some numbers now. Just like your output. But how do I show the view like my table above. I'm getting the final result but how do I display previous day and latest day values ....? 

[Jihwan_Kim]

Hi, @Anonymous 

Thank you for your feedback.

Please check the link down below. I added two more measures.

Actually, Payments Measure is the combination of the two.

https://www.dropbox.com/s/qzx3wc2kniq14oy/gumdrop.pbix?dl=0 

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Linkedin: linkedin.com/in/jihwankim1975/

Twitter: twitter.com/Jihwan_JHKIM

[Anonymous]

thanks Kim, I applied your solution and its working like this. 

So i am getting my previous payment, and latest payment now. 

However theres a slight issue with previous payment. 

For example, I want to see 19 may and 18 may. 

The results however show previous date to ANY previous date that the last payment was made. 

For example a cutsomer couldve paid $50 on 01st May... and still owes a $100. 

$100 current overdue latest is fine, but in the previous payment record i should see $100 for 18May. Whereas in this context it takes me back to the previous date of payment made. 

I want to be able to see if they have paid anything yesterday (or previous day from latest date), if they have a value shows, if they have not paid, the value stays the same  

See what i am getting at..? is there anyway i can tweak the script to get that...? 

[Jihwan_Kim]

Hi, @Anonymous 

Thank you for your feedback.

I think I misunderstood from the perspective of blank value, zero, yesterday, lastnonblankvalue, and others. In my opinion, there is some difference in how it is handled in Power BI, especially blank vs zero. 

If it is OK with you, please share your other sample pbix file's link that has more information than the initial one, and please share how your expected result looks like.

[Anonymous]

Hello Kim, 

Thank you for your promt replies, and aplogies that i couldnt get back to you in a timely fashion. 

I think to make things easier, 

here is a code that i use to calculate the current balance owing on the latest date. 

can i change this code to look at the previous day value...?? (-1) 

I think once i can get the previous day using this code, i can work out the rest.

Thanks in advance

[Anonymous · Accepted solution]

Hi @Anonymous ,

You can create a measure as below to get the value from the previous day using the function PREVIOUSDAY:

Previous - NYD =
CALCULATE (
    SUM ( Debtor[AMOUNT] ),
    FILTER ( Debtor, Debtor[AR_BALANCE_MEASURE] = 'Current - NYD' ),
    PREVIOUSDAY ( Debtor[Date] )
)

In addition, you can refer the content in the following blog to achieve it.

Calculate last or previous value within Power BI

Best Regards

[amitchandak]

@Anonymous , You have to create two measures using date table and time intelligence

example measures

This Day = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Date]=max('Date'[Date])))
Last Day = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Date]=max('Date'[Date])-1))
Last Day = CALCULATE(sum('order'[Qty]), previousday('Date'[Date]))

diff =[This Day] - [Last Day]

[Anonymous]

Hi Amit, than you for your reply. 

I have a measure for Overdue which gives me total overdue for todays (or latest date in the dataset). 

The total overdue measure is calculated using a few measures, such as shown below.

How do i implement this TotalOverdue measure into your measure? 

is this what i should do; 

This Day = CALCULATE(sum([Total OverDue), FILTER(ALL('Date'),'Date'[Date]=max('Date'[Date])))