cancel
Showing results for
Did you mean:
Frequent Visitor

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

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))

1 ACCEPTED SOLUTION
Frequent Visitor

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)

11 REPLIES 11
Frequent Visitor

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)

Helper I

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))
)

Frequent Visitor

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.

Super User

try this,

``````Yesterday sales =
``````2days ago sales =

Thanks,

If I answered your question then mark my post as a solution and a kudo would be appreciated.
Frequent Visitor
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).
Helper V

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

Try This One

Thanks,
Thennarasu

Frequent Visitor

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.

Super User

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

Thanks,

If I answered your question then mark my post as a solution and a kudo would be appreciated.
Frequent Visitor

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

Super User

If possible, Could you share the sample data?

Thanks,

If I answered your question then mark my post as a solution and a kudo would be appreciated.
Frequent Visitor

I dont see any option to attach excel file

Announcements

#### 2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

#### Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

#### Business Application LATAM Summit 2023

Join the biggest FREE Business Applications Event in LATAM this February.

#### Global Power Platform Bootcamp

In this bootcamp we will deep-dive into Microsoft’s Power Platform stack with hands-on sessions and labs, delivered to you by experts and community leaders.

Top Solution Authors
Top Kudoed Authors