cancel
Showing results for
Did you mean:

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Anonymous
Not applicable

## DAX formula to calculate change from yesterday

I have a data set which shows available units by product and day. I would like to add a column which calculates the change in available units for each product from the previous day. What formula would I used to create this column?

1 ACCEPTED SOLUTION
Solution Sage

You can do it like this:

Difference With Yesterday =
SUMX (
SUMMARIZE ( Data, Data[SKU], Data[Day] ),
VAR ThisDay = Data[Day]
VAR YestAmount =
CALCULATE ( SUM ( Data[Available Count] ), Data[Day] = ThisDay - 1 )
RETURN
IF (
ISBLANK ( YestAmount ),
BLANK (),
YestAmount - CALCULATE ( SUM ( Data[Available Count] ) )
)
)

You cannot use the time intelligence PREVIOUSDAY function because you do not have a Date table

Proud to be a Datanaut!

6 REPLIES 6
Community Champion

Do you have seperate record for each item for each day? could you please post sample data ( in copy- pastable format) with expected output?

Thanks
Raj

Anonymous
Not applicable

Day              SKU        Available count      Daily Change
9/15/2018   111111    20
9/15/2018   111112    17
9/15/2018   111113    14
9/16/2018   111111    18                          2
9/16/2018   111112    16                          1
9/16/2018   111113    14                          0
9/17/2018   111111    13                          5
9/17/2018   111112    13                          3
9/17/2018   111113    12                          2

The last column is what I am trying to create.

Solution Sage

You can do it like this if you want to add a calculated column, even though a measure would be better

Column =
VAR PrevVal =
CALCULATE (
SUM ( Data[Available Count] ),
ALLEXCEPT (
Data,
Data[SKU]
),
Data[Day] = EARLIER ( Data[Day] ) - 1
)
RETURN
IF (
ISBLANK ( PrevVal ),
BLANK (),
PrevVal - Data[Available Count]
)

Proud to be a Datanaut!

Anonymous
Not applicable

What would be the measure formula?

Solution Sage

You can do it like this:

Difference With Yesterday =
SUMX (
SUMMARIZE ( Data, Data[SKU], Data[Day] ),
VAR ThisDay = Data[Day]
VAR YestAmount =
CALCULATE ( SUM ( Data[Available Count] ), Data[Day] = ThisDay - 1 )
RETURN
IF (
ISBLANK ( YestAmount ),
BLANK (),
YestAmount - CALCULATE ( SUM ( Data[Available Count] ) )
)
)

You cannot use the time intelligence PREVIOUSDAY function because you do not have a Date table

Proud to be a Datanaut!

Solution Sage

Have a look at the PREVIOUSDAY function

https://msdn.microsoft.com/en-us/query-bi/dax/previousday-function-dax?f=255&MSPPError=-2147217396

Proud to be a Datanaut!

Announcements

#### Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

#### Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

#### The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors