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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
Employee
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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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