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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
sajal161292
Helper V
Helper V

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
v-sihou-msft
Microsoft Employee
Microsoft Employee

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

 

 

Hi,

Thanks for your reply!!

It is still showing incorrect data in some places

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)

 

 

Hi,

 

Can someone please respond?

Hi,

 

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

sajal161292
Helper V
Helper V

Hi,

 

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

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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