Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Don't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.

Reply
fatconductor
Frequent Visitor

Continue last result in calculation

‎05-17-2022 08:03 PM

Hi there, 

I feel like I'm 80% there with this solution but just need a little bit more help.

I'm looking to project my forward inventory, based on the last available data, into the future. The Inventory value in the future would be:
"Projected SOH = Last weeks Network SOH - Adjusted FC + On Order"
In the below screenshot I've managed to get last weeks inventory from week 46 into week 47, and applied the formula to it WHICH WORKS!

fatconductor_0-1652842401931.png


My issue now is using that result in the forward calculation, I'm fairly new to DAX so I don't know if there is a key step or element I'm missing. I'm also using an IF funtion to land on the last week number so I'm not sure if there's a cleaner way to do that.

Is there a way to use the last result from my measure into future calculations of the same measure? I've tried re-doing it with a running total but can't seem to get anywhere.

 

Last Weeks OH = 
if([VALUE for Network SOH]=0,
    calculate(
    SUM('Forecast Data'[VALUE]),
    'Forecast Data'[MEASURE] IN { "Network SOH" },
    filter(       ALL ( 'Date' ),
        'Date'[Year] = MAX ( 'Date'[Year] )
            && 'Date'[weekIndex]
                = MAX ( 'Date'[weekIndex] ) - 1))+'Forecast Data'[VALUE for On Order]-'FC Adj %'[Adjusted FC],
0)

 

 

My current main data set is below. I've created a date calendar with an index column to find last weeks data.
 

fatconductor_1-1652842854579.png


Please any help anyone could give would be appreciated I've spent some late nights lately trying to rework things found in other posts.

 

Appreciate your help in advance

Labels:
Message 1 of 8
760 Views
0
1 ACCEPTED SOLUTION
Anonymous
Not applicable

‎05-24-2022 11:45 PM

Hi @fatconductor ,

Please have a try.

Create a measure.

Measure_1 =
CALCULATE (
    SUM ( 'Test Data'[VALUE] ),
    FILTER (
        ALL ( 'Test Data' ),
        'Test Data'[VERSION] = "Actual/Forecast"
            && 'Test Data'[FIN_WEEK] = SELECTEDVALUE ( 'Test Data'[FIN_WEEK] )
            && 'Test Data'[MEASURE] = SELECTEDVALUE ( 'Test Data'[MEASURE] )
    )
)

vpollymsft_0-1653461041564.png

Best Regards

Community Support Team _ Polly

 

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

 

View solution in original post

Continue last result in calculation.pbix
Message 8 of 8
586 Views
0
7 REPLIES 7
Anonymous
Not applicable

‎05-24-2022 11:45 PM

Hi @fatconductor ,

Please have a try.

Create a measure.

Measure_1 =
CALCULATE (
    SUM ( 'Test Data'[VALUE] ),
    FILTER (
        ALL ( 'Test Data' ),
        'Test Data'[VERSION] = "Actual/Forecast"
            && 'Test Data'[FIN_WEEK] = SELECTEDVALUE ( 'Test Data'[FIN_WEEK] )
            && 'Test Data'[MEASURE] = SELECTEDVALUE ( 'Test Data'[MEASURE] )
    )
)

vpollymsft_0-1653461041564.png

Best Regards

Community Support Team _ Polly

 

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

 

Continue last result in calculation.pbix
Message 8 of 8
587 Views
0
lbendlin
Super User
Super User

‎05-18-2022 04:57 PM

Power BI has no memory, and no global variables.  You need to solve this issue by using an aggregator function, specifically SUMX()  that computes your inventory movements from a base date to the current filter context. For every data point!

 

Please provide sanitized sample data that fully covers your issue. If you paste the data into a table in your post or use one of the file services it will be easier to assist you. Avoid posting screenshots of your source data if possible.

Please show the expected outcome based on the sample data you provided. Screenshots of the expected outcome are ok.

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

Message 2 of 8
680 Views
0
fatconductor
Frequent Visitor
In response to lbendlin

‎05-18-2022 07:50 PM

Hi Ibendin,

Please see below link for sample data.

https://drive.google.com/file/d/1PK0ot2B9x5Wix3YqtRgGVjo_DYMlcZsb/view?usp=sharing

And below for expected outcome. Appreciate the help.

fatconductor_0-1652928634482.png

 

Message 5 of 8
657 Views
0
lbendlin
Super User
Super User
In response to fatconductor

‎05-19-2022 05:11 AM

Thank you for providing the sample data. That helps a lot with proposing a potential solution.

 

"Projected SOH = Last weeks Network SOH - Adjusted FC + On Order"

 

Which part of your sample data is the Adjusted Forecast?

Message 6 of 8
620 Views
0
fatconductor
Frequent Visitor
In response to lbendlin

‎05-19-2022 03:26 PM

Hi Ibendin,

In the sample data there are categorical values for "Units Sold". In the past they are sales and in the future it is forecast they are one and the same.

Adjusted FC is an % increase/decrease slider linked to "Units Sold" in the future, allowing the users to adjust the forecast by %'s and see the implications to $$ spend and (hopefully) Projected SOH.

It is a nice to have in this solution, but not imperative. I can always try to hack it on later. 

"Projected SOH = Last weeks Network SOH - Units Sold + On Order" is also fine.

 

Thanks for your help

Message 7 of 8
613 Views
0
fatconductor
Frequent Visitor
In response to lbendlin

‎05-18-2022 07:48 PM

Hey there,

 

Thanks so much for the help Ibendin. Please see below sample data.

VERSION	ITEM_NO	LOCATION	FIN_WEEK	MEASURE	VALUE
Actual/Forecast	109382	2560	2022 Week 30	Network SOH	2299
Actual/Forecast	109382	5520	2022 Week 30	Network SOH	1273
Actual/Forecast	109382	6560	2022 Week 30	Network SOH	2264
Actual/Forecast	109382	7350	2022 Week 30	Network SOH	6527
Actual/Forecast	109382	8350	2022 Week 30	Network SOH	2499
Actual/Forecast	109382	2560	2022 Week 31	Network SOH	2899
Actual/Forecast	109382	5520	2022 Week 31	Network SOH	1228
Actual/Forecast	109382	6560	2022 Week 31	Network SOH	1819
Actual/Forecast	109382	7350	2022 Week 31	Network SOH	10616
Actual/Forecast	109382	8350	2022 Week 31	Network SOH	3240
Actual/Forecast	109382	2560	2022 Week 32	Network SOH	2425
Actual/Forecast	109382	5520	2022 Week 32	Network SOH	912
Actual/Forecast	109382	6560	2022 Week 32	Network SOH	1979
Actual/Forecast	109382	7350	2022 Week 32	Network SOH	9285
Actual/Forecast	109382	8350	2022 Week 32	Network SOH	2783
Actual/Forecast	109382	2560	2022 Week 33	Network SOH	2657
Actual/Forecast	109382	5520	2022 Week 33	Network SOH	767
Actual/Forecast	109382	6560	2022 Week 33	Network SOH	1621
Actual/Forecast	109382	7350	2022 Week 33	Network SOH	7387
Actual/Forecast	109382	8350	2022 Week 33	Network SOH	2339
Actual/Forecast	109382	2560	2022 Week 34	Network SOH	3446
Actual/Forecast	109382	5520	2022 Week 34	Network SOH	791
Actual/Forecast	109382	6560	2022 Week 34	Network SOH	3297
Actual/Forecast	109382	7350	2022 Week 34	Network SOH	6030
Actual/Forecast	109382	8350	2022 Week 34	Network SOH	2353
Actual/Forecast	109382	6560	2022 Week 30	On Order	4608
Actual/Forecast	109382	7350	2022 Week 30	On Order	768
Actual/Forecast	109382	6560	2022 Week 31	On Order	3840
Actual/Forecast	109382	8350	2022 Week 31	On Order	4608
Actual/Forecast	109382	7350	2022 Week 33	On Order	3072
Actual/Forecast	109382	8350	2022 Week 35	On Order	3840
Actual/Forecast	109382	7350	2022 Week 36	On Order	5376
Actual/Forecast	109382	8350	2022 Week 36	On Order	2304
Actual/Forecast	109382	6560	2022 Week 37	On Order	768
Actual/Forecast	109382	8350	2022 Week 37	On Order	3072
Actual/Forecast	109382	6560	2022 Week 38	On Order	3840
Actual/Forecast	109382	7350	2022 Week 38	On Order	7680
Actual/Forecast	109382	6560	2022 Week 39	On Order	10752
Actual/Forecast	109382	7350	2022 Week 39	On Order	8448
Actual/Forecast	109382	8350	2022 Week 39	On Order	4608
Actual/Forecast	109382	2560	2022 Week 30	Units Sold	302
Actual/Forecast	109382	5520	2022 Week 30	Units Sold	289
Actual/Forecast	109382	6560	2022 Week 30	Units Sold	634
Actual/Forecast	109382	7350	2022 Week 30	Units Sold	1532
Actual/Forecast	109382	8350	2022 Week 30	Units Sold	735
Actual/Forecast	109382	2560	2022 Week 31	Units Sold	168
Actual/Forecast	109382	5520	2022 Week 31	Units Sold	276
Actual/Forecast	109382	6560	2022 Week 31	Units Sold	603
Actual/Forecast	109382	7350	2022 Week 31	Units Sold	1171
Actual/Forecast	109382	8350	2022 Week 31	Units Sold	454
Actual/Forecast	109382	2560	2022 Week 32	Units Sold	380
Actual/Forecast	109382	5520	2022 Week 32	Units Sold	406
Actual/Forecast	109382	6560	2022 Week 32	Units Sold	570
Actual/Forecast	109382	7350	2022 Week 32	Units Sold	1634
Actual/Forecast	109382	8350	2022 Week 32	Units Sold	767
Actual/Forecast	109382	2560	2022 Week 33	Units Sold	532
Actual/Forecast	109382	5520	2022 Week 33	Units Sold	177
Actual/Forecast	109382	6560	2022 Week 33	Units Sold	491
Actual/Forecast	109382	7350	2022 Week 33	Units Sold	1983
Actual/Forecast	109382	8350	2022 Week 33	Units Sold	663
Actual/Forecast	109382	2560	2022 Week 34	Units Sold	460
Actual/Forecast	109382	5520	2022 Week 34	Units Sold	214
Actual/Forecast	109382	6560	2022 Week 34	Units Sold	527
Actual/Forecast	109382	7350	2022 Week 34	Units Sold	1602
Actual/Forecast	109382	8350	2022 Week 34	Units Sold	461
Actual/Forecast	109382	2560	2022 Week 35	Units Sold	434
Actual/Forecast	109382	6560	2022 Week 35	Units Sold	1593
Actual/Forecast	109382	7350	2022 Week 35	Units Sold	1786
Actual/Forecast	109382	8350	2022 Week 35	Units Sold	1013
Actual/Forecast	109382	2560	2022 Week 36	Units Sold	437
Actual/Forecast	109382	6560	2022 Week 36	Units Sold	1604
Actual/Forecast	109382	7350	2022 Week 36	Units Sold	1798
Actual/Forecast	109382	8350	2022 Week 36	Units Sold	1021
Actual/Forecast	109382	2560	2022 Week 37	Units Sold	420
Actual/Forecast	109382	6560	2022 Week 37	Units Sold	1540
Actual/Forecast	109382	7350	2022 Week 37	Units Sold	1727
Actual/Forecast	109382	8350	2022 Week 37	Units Sold	980
Actual/Forecast	109382	2560	2022 Week 38	Units Sold	422
Actual/Forecast	109382	6560	2022 Week 38	Units Sold	1546
Actual/Forecast	109382	7350	2022 Week 38	Units Sold	1734
Actual/Forecast	109382	8350	2022 Week 38	Units Sold	984
Actual/Forecast	109382	2560	2022 Week 39	Units Sold	391
Actual/Forecast	109382	6560	2022 Week 39	Units Sold	1434
Actual/Forecast	109382	7350	2022 Week 39	Units Sold	1608
Actual/Forecast	109382	8350	2022 Week 39	Units Sold	913

 

Message 4 of 8
618 Views
0
fatconductor
Frequent Visitor
In response to lbendlin

‎05-18-2022 05:36 PM

Hey mate,

Thanks so much I'll try and work SUMX into the formula and see if that works. In the meantime sample data is below with an expected outcome screenshot. 

fatconductor_0-1652920566762.png

 

 

VERSION	ITEM_NO	LOCATION	FIN_WEEK	MEASURE	VALUE
Actual/Forecast	109382	2560	2022 Week 30	Network SOH	2299
Actual/Forecast	109382	5520	2022 Week 30	Network SOH	1273
Actual/Forecast	109382	6560	2022 Week 30	Network SOH	2264
Actual/Forecast	109382	7350	2022 Week 30	Network SOH	6527
Actual/Forecast	109382	8350	2022 Week 30	Network SOH	2499
Actual/Forecast	109382	2560	2022 Week 31	Network SOH	2899
Actual/Forecast	109382	5520	2022 Week 31	Network SOH	1228
Actual/Forecast	109382	6560	2022 Week 31	Network SOH	1819
Actual/Forecast	109382	7350	2022 Week 31	Network SOH	10616
Actual/Forecast	109382	8350	2022 Week 31	Network SOH	3240
Actual/Forecast	109382	2560	2022 Week 32	Network SOH	2425
Actual/Forecast	109382	5520	2022 Week 32	Network SOH	912
Actual/Forecast	109382	6560	2022 Week 32	Network SOH	1979
Actual/Forecast	109382	7350	2022 Week 32	Network SOH	9285
Actual/Forecast	109382	8350	2022 Week 32	Network SOH	2783
Actual/Forecast	109382	2560	2022 Week 33	Network SOH	2657
Actual/Forecast	109382	5520	2022 Week 33	Network SOH	767
Actual/Forecast	109382	6560	2022 Week 33	Network SOH	1621
Actual/Forecast	109382	7350	2022 Week 33	Network SOH	7387
Actual/Forecast	109382	8350	2022 Week 33	Network SOH	2339
Actual/Forecast	109382	2560	2022 Week 34	Network SOH	3446
Actual/Forecast	109382	5520	2022 Week 34	Network SOH	791
Actual/Forecast	109382	6560	2022 Week 34	Network SOH	3297
Actual/Forecast	109382	7350	2022 Week 34	Network SOH	6030
Actual/Forecast	109382	8350	2022 Week 34	Network SOH	2353
Actual/Forecast	109382	6560	2022 Week 30	On Order	4608
Actual/Forecast	109382	7350	2022 Week 30	On Order	768
Actual/Forecast	109382	6560	2022 Week 31	On Order	3840
Actual/Forecast	109382	8350	2022 Week 31	On Order	4608
Actual/Forecast	109382	7350	2022 Week 33	On Order	3072
Actual/Forecast	109382	8350	2022 Week 35	On Order	3840
Actual/Forecast	109382	7350	2022 Week 36	On Order	5376
Actual/Forecast	109382	8350	2022 Week 36	On Order	2304
Actual/Forecast	109382	6560	2022 Week 37	On Order	768
Actual/Forecast	109382	8350	2022 Week 37	On Order	3072
Actual/Forecast	109382	6560	2022 Week 38	On Order	3840
Actual/Forecast	109382	7350	2022 Week 38	On Order	7680
Actual/Forecast	109382	6560	2022 Week 39	On Order	10752
Actual/Forecast	109382	7350	2022 Week 39	On Order	8448
Actual/Forecast	109382	8350	2022 Week 39	On Order	4608
Actual/Forecast	109382	2560	2022 Week 30	Units Sold	302
Actual/Forecast	109382	5520	2022 Week 30	Units Sold	289
Actual/Forecast	109382	6560	2022 Week 30	Units Sold	634
Actual/Forecast	109382	7350	2022 Week 30	Units Sold	1532
Actual/Forecast	109382	8350	2022 Week 30	Units Sold	735
Actual/Forecast	109382	2560	2022 Week 31	Units Sold	168
Actual/Forecast	109382	5520	2022 Week 31	Units Sold	276
Actual/Forecast	109382	6560	2022 Week 31	Units Sold	603
Actual/Forecast	109382	7350	2022 Week 31	Units Sold	1171
Actual/Forecast	109382	8350	2022 Week 31	Units Sold	454
Actual/Forecast	109382	2560	2022 Week 32	Units Sold	380
Actual/Forecast	109382	5520	2022 Week 32	Units Sold	406
Actual/Forecast	109382	6560	2022 Week 32	Units Sold	570
Actual/Forecast	109382	7350	2022 Week 32	Units Sold	1634
Actual/Forecast	109382	8350	2022 Week 32	Units Sold	767
Actual/Forecast	109382	2560	2022 Week 33	Units Sold	532
Actual/Forecast	109382	5520	2022 Week 33	Units Sold	177
Actual/Forecast	109382	6560	2022 Week 33	Units Sold	491
Actual/Forecast	109382	7350	2022 Week 33	Units Sold	1983
Actual/Forecast	109382	8350	2022 Week 33	Units Sold	663
Actual/Forecast	109382	2560	2022 Week 34	Units Sold	460
Actual/Forecast	109382	5520	2022 Week 34	Units Sold	214
Actual/Forecast	109382	6560	2022 Week 34	Units Sold	527
Actual/Forecast	109382	7350	2022 Week 34	Units Sold	1602
Actual/Forecast	109382	8350	2022 Week 34	Units Sold	461
Actual/Forecast	109382	2560	2022 Week 35	Units Sold	434
Actual/Forecast	109382	6560	2022 Week 35	Units Sold	1593
Actual/Forecast	109382	7350	2022 Week 35	Units Sold	1786
Actual/Forecast	109382	8350	2022 Week 35	Units Sold	1013
Actual/Forecast	109382	2560	2022 Week 36	Units Sold	437
Actual/Forecast	109382	6560	2022 Week 36	Units Sold	1604
Actual/Forecast	109382	7350	2022 Week 36	Units Sold	1798
Actual/Forecast	109382	8350	2022 Week 36	Units Sold	1021
Actual/Forecast	109382	2560	2022 Week 37	Units Sold	420
Actual/Forecast	109382	6560	2022 Week 37	Units Sold	1540
Actual/Forecast	109382	7350	2022 Week 37	Units Sold	1727
Actual/Forecast	109382	8350	2022 Week 37	Units Sold	980
Actual/Forecast	109382	2560	2022 Week 38	Units Sold	422
Actual/Forecast	109382	6560	2022 Week 38	Units Sold	1546
Actual/Forecast	109382	7350	2022 Week 38	Units Sold	1734
Actual/Forecast	109382	8350	2022 Week 38	Units Sold	984
Actual/Forecast	109382	2560	2022 Week 39	Units Sold	391
Actual/Forecast	109382	6560	2022 Week 39	Units Sold	1434
Actual/Forecast	109382	7350	2022 Week 39	Units Sold	1608
Actual/Forecast	109382	8350	2022 Week 39	Units Sold	913


Really appreciate the help here thankhou.

Message 3 of 8
618 Views
0

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

Jan25PBI_Carousel

Power BI Monthly Update - January 2025

Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.

Jan NL Carousel

Fabric Community Update - January 2025

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

View All