Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
MuhamadIlhamsya
Frequent Visitor

Calculate Difference in week horizon with 2 variables

Hi Guys,

 

I have a table like this, which have 2 key point (Week Number, and Material Number). My expected outcome is i want to have a differences qty of each week and material. The differences is coming from the RT Value.

 

MuhamadIlhamsya_0-1680081256493.png

i had tried some formula like this 

Diff = MAXX(FILTER('Supply_Chain Inventory_Forecast', 'Supply_Chain Inventory_Forecast'[Week Number] = EARLIER('Supply_Chain Inventory_Forecast'[Week Number])-1), 'Supply_Chain Inventory_Forecast'[RT])
but this formula would sum only for the week but not the material number
 
Expected outcome i build in excel
 
MuhamadIlhamsya_1-1680081892328.png

 

 
Really appreciated for the help guys!
1 ACCEPTED SOLUTION

Hi @MuhamadIlhamsya 

Is the output you want?

vxinruzhumsft_0-1680241271490.png

The calculated column

Diff = var a=FILTER('Supply_Chain Inventory_Forecast',[Week Number]=EARLIER('Supply_Chain Inventory_Forecast'[Week Number])-1&&[Material Number]=EARLIER('Supply_Chain Inventory_Forecast'[Material Number]))
var b=MINX(FILTER(a,'Supply_Chain Inventory_Forecast'[ReceiptDate]=MINX(a,[ReceiptDate])),[RT])
return [RT]-b

 

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

4 REPLIES 4
MuhamadIlhamsya
Frequent Visitor

Hi @v-xinruzhu-msft 

 

Thanks for the help, the formula works and the output same with my expectation.

 

Regards,

Ilhamsyah

MuhamadIlhamsya
Frequent Visitor

Hi @v-xinruzhu-msft 

 

Thanks for the help, i try the formula and it works but it works on the grouped table for all the week.

How about if i have this table like this

 

MuhamadIlhamsya_0-1680235835458.png

i try to use your formula but it seems the difference it's not same as it have multiple weeks in the same material number and i have many different material number.

My expectation is it can show the difference for each of material number from previous week but specifically at the last date of that particular week (Date is from Receipt Date column)

In the result i could know if there is significant qty drop from each week, which material number that contribute the most to the reduction.

 

Need your help for this.

 

Thanks

Regards,

Ilhamsyah

 

 

Hi @MuhamadIlhamsya 

Is the output you want?

vxinruzhumsft_0-1680241271490.png

The calculated column

Diff = var a=FILTER('Supply_Chain Inventory_Forecast',[Week Number]=EARLIER('Supply_Chain Inventory_Forecast'[Week Number])-1&&[Material Number]=EARLIER('Supply_Chain Inventory_Forecast'[Material Number]))
var b=MINX(FILTER(a,'Supply_Chain Inventory_Forecast'[ReceiptDate]=MINX(a,[ReceiptDate])),[RT])
return [RT]-b

 

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

v-xinruzhu-msft
Community Support
Community Support

Hi  @MuhamadIlhamsya 

You can refer to the following calculated column

Diff = var a=MAXX(FILTER('Supply_Chain Inventory_Forecast',[Week Number]=EARLIER('Supply_Chain Inventory_Forecast'[Week Number])-1&&[Material Number]=EARLIER('Supply_Chain Inventory_Forecast'[Material Number])),[RT])
return IF(a<>BLANK(),[RT]-a)

vxinruzhumsft_0-1680228533141.png

 

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.