cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
omkarsawant30
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))

 

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

1 ACCEPTED SOLUTION
omkarsawant30
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)

View solution in original post

11 REPLIES 11
omkarsawant30
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)

ghaines
Helper I
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))
)

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
Super User
Super User

@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,

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

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

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

Try This One

Thanks,
Thennarasu

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.

@omkarsawant30 ,

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.

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

@omkarsawant30 ,

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.

I dont see any option to attach excel file

Helpful resources

Announcements
Carousel_PBI_Wave1

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.

Power BI Summit Carousel 2

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!

BizApps LATAM 2023

Business Application LATAM Summit 2023

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

Power Platform Bootcamp

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.