To Calculate Yesterday's result and 2 days ago's result

[omkarsawant30]

I am using the below formula to calculate Product Revenue as Month to Date (MTD) for the current year.

This Year Product Revenue = TOTALMTD(SUM('Product All'[Product Revenue]),DATEADD(FILTER(DATESMTD('Product All'[Date]),'Product All'[Date]<TODAY()),0,year))

Please help me with the formula for calculating Yesterday's result and 2 days ago's result.

[omkarsawant30 · Accepted solution]

I finally got the correct formula from my colleague as shown below,

Yesterday's Product Revenue = CALCULATE(SUM('Product All'[Product Revenue]),'Product All'[Date]=TODAY()-1)

[ghaines]

Do you just want whatever the MTD result was yesterday and the day before?  You could use the same formula as before but with 'Product All'[Date] < TODAY() - 1, with an exception for the days before you would have a result e.g.:

Yesterday Result = 
IF(DAY(TODAY()) = 1, 0,

TOTALMTD(SUM('Product All'[Product Revenue]),DATEADD(FILTER(DATESMTD('Product All'[Date]),'Product All'[Date]<TODAY() - 1),0,year))
)

[omkarsawant30]

I dont want the MTD result, , i just want formula for yesterday's result for Product Revenue. Once i get that, i can use -1 in the formula to achieve the day before.

[Arul]

@omkarsawant30 ,

try this,

Yesterday sales = 
CALCULATE(SUM(Test[Sales]),DATEADD('Table B'[Date],-1,DAY))

2days ago sales = 
CALCULATE(SUM(Test[Sales]),DATEADD('Table B'[Date],-2,DAY))

Thanks,

[omkarsawant30]

Hi @Arul , i tried using your formula as below

Yesterday's Product Revenue = CALCULATE(SUM('Product All'[Product Revenue]),DATEADD('Product All'[Date],-1,DAY))

but it's not giving the expected output, it shows output as (Blank).

[Thennarasu_R]

@omkarsawant30 
Yesterday's Product Revenue = CALCULATE(SUMX(All('Product'), '[Product Revenue]),DATEADD(MaxX('Product '[Date]),-1,DAY,))

Try This One

Thanks,
Thennarasu

[omkarsawant30]

Hi @Thennarasu_R , i tried your formula, but it shows an error.

Yesterday's Product Revenue = CALCULATE(SUMX(All('Product All'[Product Revenue]),DATEADD(MaxX('Product All'[Date]),-1,DAY,)))

Too few arguments were passed to the MAXX function. The minimum argument count for the function is 2.

[Arul]

@omkarsawant30 ,

What is the visual that you are outputing the values? and What is the slicer values in your report page?

Thanks,

[omkarsawant30]

I am using a Card to show the output number. There are no slicers, just a filter for Country.

[Arul]

@omkarsawant30 ,

If possible, Could you share the sample data?

Thanks,

[omkarsawant30]

I dont see any option to attach excel file