cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
HELLO_BI2 Frequent Visitor
Frequent Visitor

Help with current formula

Hello power bi community, I created this measure to subtract 2 days worth of information, my measure has been working but recently since there have been some days where information is not recorded the measure does not seem to do what its supposed to. For example if on the 1st something is recorded but on the 2nd nothing is recorded the measure does not work. How can i get the measure to subtract no matter what.

This is my measure;

 

VAR __YESTERDAY =
CALCULATE(
SUM('TABLE'[Amount ]),
LASTDATE('TABLE'[Value Date]))
VAR __DAYB4YEST =
CALCULATE(
SUM('TABLE'[Amount ]),
LASTDATE('TABLE'[Value Date]-1))
RETURN
(__YESTERDAY - __DAYB4YEST)

 

For example; 

sample.JPGthe variance is the desired output. Unfortunately since its a transaction on days where there is no movement on an account my formula does not subtract it looks like this.Capture1.JPGthis is how it should appear.

 

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Help with current formula

Hi

Looks working fine.

 

Last date.PNG

 

Lastdate( valuedate-1) is not working, so i just hardcoded that data for testing.

 

Thanks

Raj

4 REPLIES 4
Super User
Super User

Re: Help with current formula

Can you please post some sample data and expected output?

Community Support Team
Community Support Team

Re: Help with current formula

Hi @HELLO_BI2,

Can you provide sample data? Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

 

Regards,

Daniel He

Community Support Team _ Daniel He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
HELLO_BI2 Frequent Visitor
Frequent Visitor

Re: Help with current formula

hello thank you for the reply, I have updated my post. 

Super User
Super User

Re: Help with current formula

Hi

Looks working fine.

 

Last date.PNG

 

Lastdate( valuedate-1) is not working, so i just hardcoded that data for testing.

 

Thanks

Raj