cancel
Showing results 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

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

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,

Proud to be a Super User!

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

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

Proud to be a Super User!

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,

Proud to be a Super User!

Frequent Visitor

I dont see any option to attach excel file

Announcements

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

#### Power BI Monthly Update - February 2024

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

#### Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

#### Microsoft Fabric Partner Community

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

Top Solution Authors
Top Kudoed Authors