Get latest Value until week

[Giswald]

Hi I run into one problem at work here and through hours of googling and video-watching I could not find any answer that fit me.

I have a Customer-Article-Date-InStock Table:

CustomerID	ArticleID	Date	InStock
1	1	27.07.2021	1
1	1	28.07.2021	0
1	1	13.07.2021	1

From the Date I know the week numbere.

The "InStock" should be summarized per week. If no matching value is found for this week take the previous one (Could be any week before this).

So basically this is a change table and not a fact table.

I would expect:

Week 28: 1

Week 29: 1

Week 30: 0

How can I do this?

[v-yetao1-msft]

Hi @Giswald 

I don't quite understand the expected results you provided. What do you mean by no matching value ? How to judge ? Can you provide a more detailed data or pbix file to help us deal with problems better ?

Best Regards

Community Support Team _ Ailsa Tao

[lbendlin]

Have a separate (disconnected) dates table with the week ranges and then do a cross join between it and your not-a-facts table to drive the measures.  You may want to provide some more sample data and indicate the expected result so the premise is clearer.