topic Re: How to show latest SOH from latest week only in DAX Commands and Tips

How to show latest SOH from latest week only

Lisaal95 — Thu, 14 Sep 2023 01:39:17 GMT

What measure an I use that shows results only if the data for the latest week is available.

For example I only want the SOH to show for the 10/09/23 date which is the latest week the rest should be nil.

	Latest Report Date	Latest SOH
Customer1	10/09/2023	100
Customer2	3/09/2023	200
Customer3	10/09/2023	300
Customer4	3/09/2023	400
Customer5	10/09/2023	500

Re: How to show latest SOH from latest week only

DataNinja777 — Thu, 14 Sep 2023 03:52:45 GMT

Hi Lisaal95 ,

The dax measure like below will generate the output you require.

I didn't properly build a data model for this task (i.e., no separate dimension table was created), but I attach the link to the pbix file below:

Lastdate.pbix

Re: How to show latest SOH from latest week only

Lisaal95 — Thu, 14 Sep 2023 23:41:51 GMT

Thank you however, I want to see the SOH units by Customer that are available excluding any old week data if possible. Need to see by customer and item code. Ideally if certain customers have not provided latest week data it will not appear in the results.

Re: How to show latest SOH from latest week only

DataNinja777 — Fri, 15 Sep 2023 02:17:16 GMT

Hi Lisaal95 ,

I've remodeled the data model and properly created a separate calendar table as a date dimension table this time instead of just using the date field in the fact table to achieve your required output. (It is always a best practice to properly prepare the data model with star schema rather than trying to jumble together everything in a fact table without properly creating a relationship with a dimension table like in my first response. 🙂

The current data model looks like below:

This produces your required output as shown below:

I attach the link to the updated pbix file.

Lastdate.pbix

Re: How to show latest SOH from latest week only

Lisaal95 — Fri, 15 Sep 2023 04:18:11 GMT

Thanks, i tried this before and not getting the required output. Reason being the Latest date is not always the report date. We have sales and customer sell-through. Sales are uploaded daily but customer sell-through reports are uploaded weekly. Need the latest date from the customer sell-through only. For example, if i run this measure today the latest date in the dataset will be from yesterday not end of the prior week.

Re: How to show latest SOH from latest week only

DataNinja777 — Fri, 15 Sep 2023 05:19:57 GMT

Hi Lisaal95

It sounds like your task can be accomplished by various different means. I don't know your exact data model, but what about experimenting with lastnonblank function?