Hi Team,
I'm wondering if it's possible to create a DAX formula that will calculate days of supply, as shown in the example below:
[TABLE="width: 610"]
<tbody>[TR]
[TD][/TD]
[TD]Week 1[/TD]
[TD]Week 2[/TD]
[TD]Week 3[/TD]
[TD]Week 4[/TD]
[TD]Week 5[/TD]
[TD]Week 6[/TD]
[TD]Week 7[/TD]
[TD]Week 8[/TD]
[/TR]
[TR]
[TD]Demand[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]21[/TD]
[/TR]
[TR]
[TD]Supply[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]30[/TD]
[/TR]
[TR]
[TD]Opening Stock[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]40[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]58[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]15[/TD]
[/TR]
[TR]
[TD]Closing Stock[/TD]
[TD="align: right"]40[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]58[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]24[/TD]
[/TR]
[TR]
[TD]Days of Supply[/TD]
[TD="align: right"]28.0[/TD]
[TD="align: right"]21.0[/TD]
[TD="align: right"]14.0[/TD]
[TD="align: right"]26.0[/TD]
[TD="align: right"]19.0[/TD]
[TD="align: right"]12.0[/TD]
[TD="align: right"]5.0[/TD]
[TD="align: right"]0.0[/TD]
[/TR]
</tbody>[/TABLE]
It is possible to do it in Excel, but it relies on data being arranged in a crosstab in order for the formula to be able to calculate the forward buckets of demand covered.
Bit of a long shot I'm thinking, but I thought I'd ask the question in case anyone has any bright ideas.
Cheers,
Matty
I'm wondering if it's possible to create a DAX formula that will calculate days of supply, as shown in the example below:
[TABLE="width: 610"]
<tbody>[TR]
[TD][/TD]
[TD]Week 1[/TD]
[TD]Week 2[/TD]
[TD]Week 3[/TD]
[TD]Week 4[/TD]
[TD]Week 5[/TD]
[TD]Week 6[/TD]
[TD]Week 7[/TD]
[TD]Week 8[/TD]
[/TR]
[TR]
[TD]Demand[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]21[/TD]
[/TR]
[TR]
[TD]Supply[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]30[/TD]
[/TR]
[TR]
[TD]Opening Stock[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]40[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]58[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]15[/TD]
[/TR]
[TR]
[TD]Closing Stock[/TD]
[TD="align: right"]40[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]58[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]24[/TD]
[/TR]
[TR]
[TD]Days of Supply[/TD]
[TD="align: right"]28.0[/TD]
[TD="align: right"]21.0[/TD]
[TD="align: right"]14.0[/TD]
[TD="align: right"]26.0[/TD]
[TD="align: right"]19.0[/TD]
[TD="align: right"]12.0[/TD]
[TD="align: right"]5.0[/TD]
[TD="align: right"]0.0[/TD]
[/TR]
</tbody>[/TABLE]
It is possible to do it in Excel, but it relies on data being arranged in a crosstab in order for the formula to be able to calculate the forward buckets of demand covered.
Bit of a long shot I'm thinking, but I thought I'd ask the question in case anyone has any bright ideas.
Cheers,
Matty