Forum Discussion

SAM190370's avatar
SAM190370
Frequent Visitor
9 years ago
Solved

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 sh...
Need Help
  • ImkeF's avatar
    ImkeF
    9 years ago

    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"
kaushikd's avatar
kaushikd
Resolver II
9 years ago

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.

 

SAM190370's avatar
SAM190370
Frequent Visitor
9 years ago

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. 

  • ImkeF's avatar
    ImkeF
    Community Champion
    9 years ago

    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"
    • SAM190370's avatar
      SAM190370
      Frequent Visitor
      9 years ago

      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's avatar
        SAM190370
        Frequent Visitor
        9 years ago

        Hi Imke,

         

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

         

        / Søren

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      What if you have more than one type of sales? 

       

      • ImkeF's avatar
        ImkeF
        Community Champion
        6 years ago

        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.