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

To compute some rows dynamically

Hi,

 

I am new to Power BI and stuck up while re-computing the value of a field in my report.

 

I need to adjust the value for the field On hand in the current week as per the previous week's cumulative total value(Demand+Inbound Supply+On Hand).

 

Please find the screenshots below.

 

InkedCapture8_LI.jpgInkedcapture9_LI.jpg

As an example

Week 47:

On Hand = Total of previous week(46) = 557.72

 

Week 48:

On Hand = Total of previous week(47) = -993.83

 

Similarly for the other weeks as well.

 

Also as we are adjusting the value for on hand field then the cumulative total will also get updated accordingly.I need to implement this in one of my report very urgently.

 

Please find the link for the test file aslo : https://drive.google.com/drive/folders/12Ou3PcwDie0aju7ZV8ISSbeKsvXdZ6Vp

 

Any help will be greatly appreciated!!

 

7 REPLIES 7
v-jiascu-msft
Employee
Employee

Hi @sajal161292,

 

I'm afraid we can't do it with DAX. Let me make some explanations.

1. Make it simpler - Total quantity of last week. We could do it this way.

QuantityOfLastweek =
CALCULATE (
    SUM ( 'ShortageData'[Quantity] ),
    FILTER ( ALL ( 'Calendar' ), 'Calendar'[Week] = 'Calendar'[Week] - 1 )
)

2. Your scenario. 

Week 47 of QuantityOfLastweek = Week 46 of (Demand + Inbound Supply + On Hand). 

The problem is no formula for Week 46 of On Hand. Because it depends on Week 45 and recurs endlessly.

 

Maybe I missed some conditions. Do you mind adding a new order type "Total"? This could be helpful.

 

Best Regards!

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi,

 

Thank  you for the reply!!

 

1.I have added the measure QuantityOfLastWeek in the file but is not showing any value in the report.

 

2.I have added the Total order type in the file.

 

Please find the updated pbi  file in the link

 

https://drive.google.com/drive/folders/12Ou3PcwDie0aju7ZV8ISSbeKsvXdZ6Vp

Hi,

 

Additionally I would like to ask if there is any way to modify the query instead of writing up DAX expression for representing this calculation in the report.

 

Hi,

 

I have modified my pbi file with adding up a new measure for representing the previous week sales.

 

Now i am stuck up while re-assigning the value of previous week sales to the field Total.

Is there any way to perform iteration in DAX and traverse for each record?

 

Please find the link of new pbi file below

 

https://drive.google.com/drive/folders/12Ou3PcwDie0aju7ZV8ISSbeKsvXdZ6Vp

 

 

Thanks for your help!!

Hi,

 

Can anyone please help me with this?

I am really stuck up on it,have tried to implement this functionality in sql but it has become a way too complex.

 

I need this really badly.

 

 

Hi @sajal161292,

 

Could you please mark the proper answer as solution or share the solution if it's convenient for you? That will be a big help to the others.

 

Best Regards!
Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi @sajal161292,

 

Sorry so late. Too much work to do. I found a workaround though it isn't prefect. You can try it and may get some ideas. You can test it in this file.

Measure =
CALCULATE (
    SUMX (
        SUMMARIZE (
            '0ShortageData',
            '0ShortageData'[Org],
            '0ShortageData'[Part],
            '0Calendar'[Week],
            "value", SUM ( '0ShortageData'[Quantity] )
        ),
        [value]
    ),
    ALL ( '0ShortageData'[OrderType (groups)] ),
    FILTER ( ALL ( '0Calendar' ), '0Calendar'[Week] < MAX ( '0Calendar'[Week] ) )
)

To_compute_some_rows_dynamically

 

Best Regards,

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more 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.