Forum Discussion

Anonymous

Not applicable

7 years ago

Solved

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.

v-cherch-msft
7 years ago
Hi Anonymous

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

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

Regards,

5 Replies

Anonymous
Not applicable
7 years ago
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
  7 years ago
  I tried the formula but the "Production Sum Yesterday" measure didn't work. Can you have a look?
  - Anonymous
    Not applicable
    7 years ago
    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).
    Cheers!
    Nathan

Related content