Forum Discussion
Helper VCalculation not happening correctly for some data
Microsoft Employee
I don't recommend you use a IF statement to assign measure value to different order type. It's not real "slicing".
In your scenario, it has logical issue in [updated_qty] measure, you apply ShortageData[week]<MAX(ShortageData[week]) which is a cumulative weeks context. I suggest you use a [Year] column and a [WeekNumber] column in your table. Then your formula should be like:
updated_qty =
CALCULATE (
ShortageData[Cummulative],
FILTER (
ALLEXCEPT ( ShortageData, ShortageData[Name], ShortageData[item_segments] ),
ShortageData[Year] = MAX ( ShortageData[Year] )
&& ShortageData[week]
= MAX ( ShortageData[week] ) - 1
)
)
Regards,
Helper VHi,
Thanks for your reply!!
It is still showing incorrect data in some places
- sajal161292
Hi v-sihou-msft,
I updated my measure for updated_qty.
However can you help me create a new measure for more meaningful data for Updated_Quantity using DAX.
In this i need to assign the previous week's cumulative value (which is stored in the updated_qty field) to the next week "On Hand" order_type and it should remain same for other order types.
Please help me in performing this as it is very urgent!!
There is one more requirement as well.
We need to roll up the data across different weeks like the way illustrated in the screenshot.
If the data is not existing across any week it should retain the data of previous week.
in the example above the week 2017-52 is having quantity as -2 0 0 ,
so it should be replicated to all the subsequent weeks till the end. from 2017-53 to 2018-11)
- sajal161292
Hi,
Can someone please respond?
- sajal161292
Hi,
Any pointers for solving the problem will be sincerely appreciated!!