Forum Discussion

komaragiri's avatar
komaragiri
Frequent Visitor
11 months ago
Solved

Running Value for each column based on the previous column Value

My data source is EXCEL.  read the excel data source and create a simple Dashboard in BI.

 

Test data attached.  appreciate your help. 

 

 

4 Replies

  • komaragiri's avatar
    komaragiri
    Frequent Visitor

    lbendlin Instead of DAX for dailyremaing calc, can I create that column during the Transformation step? 

    • lbendlin's avatar
      lbendlin
      Super User
      let
          Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("TZA7DkMhDATvQo308A9MnxOkRRSvyA1yf8WQ2KHZYpZFA2Okx/1+pZyesALTzCPphf3CgmIImC0r78Koc23Nslt+C4pB6Zblh9kxIxxYHGMrB1bHBHrgkCE5T0MJmfNuAMdCe+PuEPKEdZlmIfXub0Swuwqxq7FTXF0T/414NWasa0cZTGbODw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t]),
          #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
          #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Date", type date}, {"R1", Int64.Type}, {"R2", Int64.Type}}),
          #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", (k)=> let t = Table.SelectRows(#"Changed Type",each [Date]<=k[Date])
      in 
      13749 - List.Sum(t[R1]) - List.Sum(t[R2])
      ,Int64.Type)
      in
          #"Added Custom"

      How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done". Once you examined the code, replace the entire Source step with your own source.