cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
sajal161292 Member
Member

Calculation not happening correctly for some data

Hi,

 

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

 

 

powerbi8.PNG

 

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?

 

 

6 REPLIES 6
sajal161292 Member
Member

Re: Calculation not happening correctly for some data

Hi,

 

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

Moderator v-sihou-msft
Moderator

Re: Calculation not happening correctly for some data

@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 Member
Member

Re: Calculation not happening correctly for some data

Hi,

Thanks for your reply!!

It is still showing incorrect data in some places

sajal161292 Member
Member

Re: Calculation not happening correctly for some data

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.powerbi14.PNG

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

Re: Calculation not happening correctly for some data

Hi,

 

Can someone please respond?

sajal161292 Member
Member

Re: Calculation not happening correctly for some data

Hi,

 

Any pointers for solving the problem will be sincerely appreciated!!

Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

Power BI Desktop Update - February 2020

Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors