cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
SAM190370 Frequent Visitor
Frequent Visitor

cursive calculation forecast

Hi,

 

Can I do something like this in dax or powerquery?

 

The formular should calculate the forecast using previous months sales and previous month forecast.  If no sales in previous month it should use forecast. I have tried to illustrate it below.

 Test.PNG

 

 

@ImkeF, I know you have done something very similar to this https://www.mrexcel.com/forum/power-bi/948513-conditional-recursive-calculation-%5bneed-help%5d.html so maybe you can see the light? I tried to use generate.list, but dit manage to go all the way.

 

Hope you can assist me.

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Recursive calculation forecast

This might work:

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQwMFCK1QEyjGAMUyjDCCalgI2MBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Sales = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Sales", type number}}),
    AddIndex = Table.Buffer(Table.AddIndexColumn(#"Changed Type", "Row", 1, 1)),
    ListGenerate = List.Generate(()=> 
        [Counter=1, Forecast=AddIndex[Sales]{0}],
        each [Counter] <=Table.RowCount(AddIndex),
        each [Forecast = if AddIndex{[Row=Counter]}[Sales]<>null then [Forecast]*0.7+AddIndex{[Row=Counter-1]}[Sales]*0.3 else [Forecast],
        Counter = [Counter]+1
        ]
    ),
    Forecast = Table.FromRecords(ListGenerate),
    #"Merged Queries" = Table.NestedJoin(AddIndex,{"Row"},Forecast,{"Counter"},"Forecast.1",JoinKind.LeftOuter),
    #"Expanded Forecast.1" = Table.ExpandTableColumn(#"Merged Queries", "Forecast.1", {"Forecast"}, {"Forecast"})
in
    #"Expanded Forecast.1"

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




5 REPLIES 5
kaushikd Member
Member

Re: Recursive calculation forecast

Hi @SAM190370

 

This is possible in DAX, you just have to add a calculated column

Step 1. R.Click on the table and click New column

Step 2. In the Formula bar just add this Dax code---->Column 1 = IF('Sample'[Sales],'Sample'[Sales],'Sample'[Forecast])

 

You can also do other calculation if required.

 

if this is what you required then dont forget to like this post.

 

Capture.PNG

SAM190370 Frequent Visitor
Frequent Visitor

Re: Recursive calculation forecast

@kaushikdThanks for giving it a shot!

 

Not exactly what I am looking for, but I guess I haven't been clear enough when creating the post. The trick is that I want to create the calculation of column B using DAX. 

Super User
Super User

Re: Recursive calculation forecast

This might work:

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQwMFCK1QEyjGAMUyjDCCalgI2MBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Sales = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Sales", type number}}),
    AddIndex = Table.Buffer(Table.AddIndexColumn(#"Changed Type", "Row", 1, 1)),
    ListGenerate = List.Generate(()=> 
        [Counter=1, Forecast=AddIndex[Sales]{0}],
        each [Counter] <=Table.RowCount(AddIndex),
        each [Forecast = if AddIndex{[Row=Counter]}[Sales]<>null then [Forecast]*0.7+AddIndex{[Row=Counter-1]}[Sales]*0.3 else [Forecast],
        Counter = [Counter]+1
        ]
    ),
    Forecast = Table.FromRecords(ListGenerate),
    #"Merged Queries" = Table.NestedJoin(AddIndex,{"Row"},Forecast,{"Counter"},"Forecast.1",JoinKind.LeftOuter),
    #"Expanded Forecast.1" = Table.ExpandTableColumn(#"Merged Queries", "Forecast.1", {"Forecast"}, {"Forecast"})
in
    #"Expanded Forecast.1"

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




SAM190370 Frequent Visitor
Frequent Visitor

Re: Recursive calculation forecast

Hi @ImkeF

 

Thanks a lot.  This is indeed very close.  Just one thing.  In my example in row 6 we do not have the same result.  

 

 

Maybe you have an idea how to fix this?

 

/ Søren

SAM190370 Frequent Visitor
Frequent Visitor

Re: Recursive calculation forecast

Hi Imke,

 

I was able to made the change myself and have accepted your solution.  Thanks again.

 

/ Søren

Helpful resources

Announcements
Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

PBI Community Highlights

PBI Community Highlights

Check out what's new in the Power BI Community!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Top Kudoed Authors
Users Online
Currently online: 379 members 3,527 guests
Please welcome our newest community members: