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
SAM190370
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

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"

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

View solution in original post

7 REPLIES 7
kaushikd
Resolver II
Resolver II

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

@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. 

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"

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

Anonymous
Not applicable

What if you have more than one type of sales? 

 

Hi @Anonymous ,

you'd have to turn the query into a function and apply it on the results of the table that is grouped by type of sales.

 

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

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

Hi Imke,

 

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

 

/ Søren

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.