How to calculate this formula for next 12 months when Current month supply is a what if parameter?

[Krupture]

Hi,

I have this quite simple forecasting calculation yet difficult (for me) to achieve in DAX. I want to calculate this in for the next 12 months. Demand is a static figure which can update monthly.

next month stock = (Get current month stock+ current month supply)- current month demand

Initial current month stock is a figure known (this can be in a prepopulated table or in a what if parameter), and demand for the next 12 months are known and pre-populated. 

Thank you

[Krupture]

Love it! I didn't know whether this function existed! Thank you so much! @v-yalanwu-msft 

[Krupture]

@v-yalanwu-msft Thank you much! This definietely works, but it breaks when I have report level filters. Shows how much I need to learn in DAX space.

As I understand, ALL(Table) is essential for this calculation to work. Could you please nudge me in the right direction?

[v-yalanwu-msft · Accepted solution]

Hi, @Krupture ,

You could change the measure :

Measure = -490+CALCULATE( [Max Units Per Month1]-SUM('Table'[Monthly Demand]),FILTER(ALLSELECTED('Table'),[Date]<=MAX('Table'[Date])))

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

[v-yalanwu-msft]

Hi， @Krupture ，

You could add measure.

workdays = 
COUNTROWS (
FILTER (
ADDCOLUMNS (
CALENDAR (STARTOFMONTH('Table'[Date]), EOMONTH(MAX('Table'[Date]),0) ),
"Day of Week", WEEKDAY ( [Date], 1 )),
[Day of Week] <> 1&& [Day of Week] <> 7) )

Max Units Per Month1 = [workdays]*26

Measure = -490+CALCULATE( [Max Units Per Month1]-SUM('Table'[Monthly Demand]),FILTER(ALL('Table'),[Date]<=MAX('Table'[Date])))

Then final show:

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

[Krupture]

Hi @v-yalanwu-msft 

Apologies about the delay.

Please refer to the Simulation on the Excel file.

https://1drv.ms/x/s!Apm7iqjfyCWclWHIuPpGBo_7hZbX?e=cYdwRN 

When you get this to Power BI, the only data should be used are the Months values and the Demand Data.

Rest must be calculated within Power BI dynamically (not using calculated tables or Power Query), just like the Excel does.

Thank you so much for your assistance.

[v-yalanwu-msft]

Hi, @Krupture

Is your problem solved? If so, kindly mark the proper reply as a solution to help others having the similar issue and close the case. If not, let me know and I'll try to help you further.

If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) 

[v-yalanwu-msft]

Hi, @Krupture ;

It could be dynamic, but I'm not quite sure what your data looks like, can you share a simple file and what you want to output?

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

[Krupture]

 Thank you for your reply!

Unfortunately, this is not the exact solution I was looking for.

In your response the highlighted column is in a table therefore a static.

But I want this to be a dynamic value that can change up on the user selection through a what if value like this (Through a what if value).

Max Unit supply per month= SELECTEDVALUE('Max Units Per Day:'[Max Units Per Day:]) * Working days

Then Can we use the above measure to do the calculation you have done?

Simulated Max Stock = -490+CALCULATE(SUM('Table'[Max Units per Month])-SUM('Table'[Monthly Demand]),FILTER(ALL('Table'),[Date]<=MAX('Table'[Date])))

*Simulated Max Stock = (This month's stock position + Max Units per month) - Monthly Demand

*May's Simulated Max Stock becomes June's Stock position and so forth

[v-yalanwu-msft]

Hi, @Krupture ;

You could tranpose table as follow:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCs8vylZwSawsVtJRMjKCE4YgwhiVa4RKGMCVGCjF6kQr+SZWKITmZZYUKxSkFin45ueVZAAlTS1NEKSZOZA0A5uGLIKiBkKaGCBUmkDNB5mYUwkSMQDJmliCSFNDMNsMzAa7ycQCImsKV2lqDCbNDeAmg/TGxgIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, #"2022-5-1" = _t, #"2022-6-1" = _t, #"2022-7-1" = _t, #"2022-8-1" = _t, #"2022-9-1" = _t, #"2022-10-1" = _t, #"2022-11-1" = _t, #"2022-12-1" = _t, #"2023-1-1" = _t, #"2023-2-1" = _t, #"2023-3-1" = _t, #"2023-4-1" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"2022-5-1", Int64.Type}, {"2022-6-1", Int64.Type}, {"2022-7-1", Int64.Type}, {"2022-8-1", Int64.Type}, {"2022-9-1", Int64.Type}, {"2022-10-1", Int64.Type}, {"2022-11-1", Int64.Type}, {"2022-12-1", Int64.Type}, {"2023-1-1", Int64.Type}, {"2023-2-1", Int64.Type}, {"2023-3-1", Int64.Type}, {"2023-4-1", Int64.Type}}),
    #"Demoted Headers" = Table.DemoteHeaders(#"Changed Type"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Demoted Headers",{{"Column1", type text}, {"Column2", type any}, {"Column3", type any}, {"Column4", type any}, {"Column5", type any}, {"Column6", type any}, {"Column7", type any}, {"Column8", type any}, {"Column9", type any}, {"Column10", type any}, {"Column11", type any}, {"Column12", type any}, {"Column13", type any}}),
    #"Transposed Table" = Table.Transpose(#"Changed Type1"),
    #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table", [PromoteAllScalars=true]),
    #"Changed Type2" = Table.TransformColumnTypes(#"Promoted Headers",{{"Column1", type date}, {"Work Days", Int64.Type}, {"Max Units per Month", Int64.Type}, {"Monthly", Int64.Type}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type2",{{"Column1", "Date"}, {"Monthly", "Monthly Demand"}})
in
    #"Renamed Columns"

Then in desktop create a measure.

Measure = -490+CALCULATE(SUM('Table'[Max Units per Month])-SUM('Table'[Monthly Demand]),FILTER(ALL('Table'),[Date]<=MAX('Table'[Date])))

Use the matrix  .

The final show:

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

[Krupture]

Thank you @danextian. 

I'll try to explain this a bit more.

The above simple forecasting calculation is what I would like to replicate in Power BI.

Simulated Max Supply Per Day value should be a What-IF parameter.

Max Units Per month = Workdays*Simulated Max Supply Per day

Monthly Demand is a known figure (estimation by using historical data)

Simulated Max Stock = (This month's stock position + Max Units per month) - Monthly Demand

May's Simulated Max Stock becomes June's Stock position and so forth.

I get stuck at Simulated Max Stock Calculation for each month as this cannot be done through a virtual table. This is due to Simulated Max Supply Per Day is a What-if Parameter.

I hope I have explained my problem here. In Excel this is an easy feat to achieve, and I know Power BI is not an Excel replacement. But it would be ideal if I can replicate this in Power BI as this simulation can be replicated to multiple suppliers and find how we can recover our stock position to positive territory. 

Your help is much appreciated!

[danextian]

Hi @Krupture ,

Here's how you can get your questions answered quickly - https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523#M6071...