Calculating Weekly Bucket Difference

[Anonymous]

Hello Guys,

the following challenge: 

For our inventory tracking, we build groups:

green = < 30 days

yellow = 30 - 90 days

red = > 90 days 

In Power Bi we realized that with the SWITCH statement 

Group by Age =

SWITCH(

    True(),

    fact_stock[age in days]>=90,"3. >=90 days",

    fact_stock[age in days]>=30,"2. 30 - 90 days",

     fact_stock[age in days]>=0,"1. <= 30 days"          )

-> this statement work fine. the age in days is in database. 

-> the fact_table is a weekly export with the following structure:

-> the unique ID  is an Stock unit ID

date	stock unit id	value	age in days	group by age
05.03.2023	12345679	100	23	< 30 days
05.03.2023	12345678	250	90	> 90 days
05.03.2023	12345677	100	89	30 - 90 days
05.03.2023	12345676	50	33	30 - 90 days
12.03.2023	12345679	50	30	30 - 90 days
12.03.2023	12345678	50	97	> 90 days
12.03.2023	12345677	null	null	consumed
12.03.2023	12345676	25	40	30 - 90 days
12.03.2023	12345684	75	2	< 30 days

Following questions i have to answer every monday:

- which stock units are new in comparison to previuos week 

- which stock units are consumpted in comparison to previus week?

- which stock units changed the age bucket?

- for example: stock unit 12345678 changed from 30 - 90 days into bucket > 90 days 

How should i build this in power bi?

[Ashish_Mathur]

Hi,

The last question is the most interesting one so i will take that up later.  For the first 2 questions, could you kinldy share the following:

1. Download link of the PowerBI file
2. A Calendar table with a week column in there
3. The expected result of the first 2 questions based on the sample data that you have shared in your post.

[lbendlin]

To report on things that aren't there you need to use disconnected tables and crossjoins.

Status = 
SWITCH(TRUE(),
min(Weeks[date])=CALCULATE(min(Weeks[date]),all(Weeks[date])),BLANK(),
not isblank([val]) && isblank(calculate([Val],DATEADD(Weeks[date],-7,DAY))),"new this week",
isblank([val]) && not isblank(calculate([Val],DATEADD(Weeks[date],-7,DAY))),"consumed",
[Bucket]<>(calculate([Bucket],DATEADD(Weeks[date],-7,DAY))),"bucket change")

see attached