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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

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 II
Helper II

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,





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!


LinkedIn


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,





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!


LinkedIn


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,





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!


LinkedIn


I dont see any option to attach excel file

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.