Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Variance Today vs Yesterday

I have two visuals that I want to merger and show the delta between today's production vs yesterday's production?

 

However, I'm struggling to write the DAX formula to show the delta by day in one colume called "Today vs Yesterday? Please note the Production info is a measure that was calculated using the sum of machines A, B, C.

 

Any help would be appreciated.  

 

table.PNG

1 ACCEPTED SOLUTION

Hi @Anonymous 

You may remove .[Date] of the formula.For example:

Yesterday = CALCULATE(SUM(Data[Production]),PREVIOUSDAY(Data[Dates]))

1.png

Regards,

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

5 REPLIES 5
Anonymous
Not applicable

@Anonymous  to get "yesterday" means to change the Filter Context of an expression.

 

So, you could have a Measure like this:

Production Sum = SUM(<yourtable>[Production])

 

And a second Measure like this (Note: The 2nd argument of CALCULATE changes Date Filter from the day being analyzed to the day before):

Product Sum Yesterday = CALCULATE([Production Sum], PREVIOUSDAY(<yourtable>'[Dates].[Date]))

 

And a third Measure like this:

Today vs Yesterday = [Production Sum] - [Production Sum Yesterday]

 

You could combine the code into a single measure, but the first 2 may be beneficial as well.

 

Cheers!

Nathan

Anonymous
Not applicable

I tried the formula but the "Production Sum Yesterday" measure didn't work. Can you have a look?table 2.PNG

Anonymous
Not applicable

Hi @Anonymous - 

It will work if you expand the date. Unfortunately, it doesn't look very good. Another approach is to add a separate date table. There are many scripts available to create one in DAX or M (Power Query).

Date Problem.PNG

Cheers!

Nathan

Anonymous
Not applicable

Yeah - it doesn't look user-friendly in the current format.

Hi @Anonymous 

You may remove .[Date] of the formula.For example:

Yesterday = CALCULATE(SUM(Data[Production]),PREVIOUSDAY(Data[Dates]))

1.png

Regards,

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.