Calculation not happening correctly for some data

[sajal161292]

Hi,

I have prepared a report.Please find the screenshot below:

Thr requirement was to pass the previous week's total value in one of the next week's order type.

I cretaed a dax measure for updating the quantity as follows:

Updated_Quantity = IF(ShortageData[due_date]<=MIN(ShortageData[due_date]),ShortageData[quantity],SWITCH(ShortageData[order_type_text (groups)],"Demand",ShortageData[quantity],"Inbound Supply",ShortageData[quantity],"Inventory",ShortageData[updated_qty]))

updated_qty = CALCULATE(ShortageData[Cummulative],FILTER(ALLEXCEPT(ShortageData,ShortageData[Name],ShortageData[item_segments]),ShortageData[week]<MAX(ShortageData[week])))

Cummulative =
CALCULATE(
SUM('ShortageData'[Quantity]),
FILTER(
ALLEXCEPT(ShortageData,ShortageData[name],ShortageData[item_segments],ShortageData[order_type_text]),
ShortageData[week] <= max(ShortageData[Week])
)
)

A weird thing is happening now it is showing correct data in some cases(e.g 1st two pats but not performing it correctly in the 3rd part.

Can you suggest any action for this or why is this happening for some parts?

[v-sihou-msft]

@sajal161292

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,

[sajal161292]

Hi,

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!!

[sajal161292]

Hi,

Can someone please suggest me the solution or is there any other forum for getting the answers quickly?