Need help with Measure for Yesterday variance

Need advice from the group on how to come up with this measure.

I have the fact for the Purchase transactions in Purch Fact with Received qty by date and the qty which are on dock( To be considered as received)

image

PurchaseSnapshot have the snapshot taken at the end of the day for what is to be received. Date is the date that PO will be received and the snapshot date is the time of the snapshot job. So if there is a PO that is in the system that is going to be received in the next 6 days, the PO line will be in this table for each day the snapshot is taken. An example is PO 100103 with date of Dec 20, will be showing up for each snapshot taken on 17th,18th & 19th.

image

The report need to calculate the Total qty of receipts based on the date selected. The totals are a bit tricky.
Example - If the selected date is Dec 17, Total receipts should show the total receipt up to Dec 17 and the totals for On Dock for the rest of the month also ( until Dec 31 ). I have this measure in the model.

My issue is

  1. I need to calculate the difference of Receipts from yesterday with below conditions.
    If selected date is Monday, need to calculate difference between Monday & Friday.
    If selected date is first day of month, need to show total as total for the month or take previous day
    total as 0.

If selected date is Dec 17th. result should be

image

Pbix file attached.

PO Test.pbix (77.1 KB)

1 Like

Hi,

Still i am not clear with your req but i am able to get your above req result by replacing one of your measure as you can see in the below screen shot:
image

Attaching the upadted pbix for your refrence:
PO Test.pbix (76.9 KB)

Thanks,
Anurag

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:

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

1 Like

Thanks @Anurag.
Sorry. I didn’t explain it properly.
Let me try to explain in this way.

Total receipts is calculated as sum of the all receipts till the date selected + total qty on the dock for the rest of the month. These 2 columns are part of the Purch Fact.
The challenge is Purch Fact is a moving number meaning the On dock qty reported on Monday could be actual received qty the next day.
For the difference from yesterday,
Difference of receipts reported yesterday to today ( Since the Purch Fact is moving number, need to look at the snapshot of yesterday to understand what was the on dock qty from PurchaseSnapshot)

Hi @train
Did you have a chance to look at the way that I provided above? If so, please let me know if you need anything else.

Thanks @sedhosen. Let me take a look. I missed this completely. My apologies.

1 Like

It’s great to know that you are making progress with your query @train.

Please don’t forget if your question has been answered within the forum it is important to mark your thread as ‘solved’.

We request you to kindly take time to answer the Enterprise DNA Forum User Experience Survey, We hope you’ll give your insights on how we can further improve the Support forum. Thanks!

Thanks @EnterpriseDNA, but it was actually @train query and I just wanted to help him to solve it.

Sorry about the wrong tag @sedhosen.

Following up @train if you still need any help with this post?

Hi @train, due to inactivity, a response on this post has been tagged as “Solution”. If you have any concern related to this topic, you can create a new thread.

Thanks @sedhosen .Sorry i was away out sick.

I went through the measure for Previous Workday. But, it is picking the incorrect totals.
The total is actually what is received until yesterday + On Dock Total ( This will have to be from PurchaseSnapshot b’cos the on dock qty in Purch Fact is a moving number) . SO the qty of 20 received could be the qty that was on dock the previous day.

@train
Please take a look at the following result along with the attached pbix file. Hopefully, you can find what you need inside the file.

After selecting December 17th, you’ll get the following result.

PO Test Hossein.pbix (90.7 KB)
Regards,
Hossein

1 Like

Thanks @sedhosen .

The Total On Dock Qty is inflated for all snapshot date.

For Dec 17th, total on dock is only 75. But shows as 475.

challenge is to get that on dock total for the day and previous day which is what i am struggling with