Need help with Measure for Yesterday variance

[sedhosen]

Hi @train
Thanks for posting your question.
For calculating the difference I added “Basic Date Table” to your pbix file because I needed to use some of its columns. It’s so easy to use and you can download the instruction along with the M function for this table using the below link:

Basic Date Table (Power Query M function)

Hi all, There have been requests for a basic Date table that includes week logic centered around the start- / end of week dates AND/OR fiscal weeks. Both will start on the selected Start of week day but the first creates weeks of 7 days when crossing over into the next calendar year and the second weeks with <= 7 days around each fiscal year start and -enddate, as illustrated below. . [image] . Copy this M code script into a new blank query. let fnDateTable = (StartDate as date, EndDate …

I imported the date table for dates from 12/15/2021 to 01/09/2022, marked it as a date table, established the relationships, and then changed some of your measures to be synced with the new Dates table:

Total Rcvd Qty Hossein = TOTALMTD(sum(‘Purch Fact’[Rcvd Qty]),‘Dates’[Date])

Total On Dock Qty Hossein =
CALCULATE ( SUM ( ‘Purch Fact’[On Dock] ),
DATESBETWEEN ( ‘Dates’[Date],
STARTOFMONTH ( ‘Dates’[Date] ),
ENDOFMONTH ( ‘Dates’[Date] ) ) )

Total Qty Hossein = [Total Rcvd Qty Hossein] + [Total On Dock Qty Hossein]

After that, I used the below measure to calculate the previous workday Qty:

Previous Workday Qty Hossein =
VAR SelectedDate = SELECTEDVALUE ( Dates[Date] )

VAR YesterdayDate = SELECTEDVALUE ( ‘Dates’[Date] ) - 1

VAR DayInWeek = CALCULATE (
VALUES ( Dates[DayInWeek] ), FILTER ( Dates, Dates[Date] = SELECTEDVALUE ( Dates[Date] ) ) )

VAR DayOfMonth = CALCULATE ( VALUES ( Dates[DayOfMonth] ),
FILTER ( Dates, Dates[Date] = SELECTEDVALUE ( Dates[Date] ) ) )

VAR Result = IF (
OR ( DayInWeek = 1, DayOfMonth = 1 ),
IF ( DayOfMonth = 1,
0,
CALCULATE (
[Total Qty Hossein],
FILTER ( ALL ( ‘Dates’ ), Dates[Date] = SelectedDate - 3 ) )
),
CALCULATE (
[Total Qty Hossein],
FILTER ( ALL ( ‘Dates’ ), Dates[Date] = YesterdayDate ) ) )

RETURN
Result

The Result was this:

Attached you may find the pbix file:
PO Test Hossein.pbix (91.2 KB)

Hope it helps you.
Regards,
Hossein