Forum Discussion

vincentpit's avatar
vincentpit
Frequent Visitor
8 years ago
Solved

Rolling Calculation

Hello Guys.

 

Im trying to figure out how to use a rolling amount calculation. I tried the following formula:

 

Rolling_up = CALCULATE(SUM('excel-planning production_progress'[boxes_done]);FILTER(ALL('excel-planning production_progress'[last_change].[Date]);'excel-planning production_progress'[last_change].[Date]<=MAX('excel-planning production_progress'[last_change].[Date])))

 

Instead of getting the cumulative value i'm getting a copy of the [boxes_done] value.

The rolling up value should be: 12, 56, 67, 100 .... etc.

 

Thanks in advance for your help!

 

Vincent Pit

 

Need Help
  • vincentpit's avatar
    vincentpit
    8 years ago

    Hi Smoupre, thanks for the quick response!

     

    I've tried quick measure, which gives me the following formula:

    cumulative =
    CALCULATE(
    SUM('excel-planning production_progress'[boxes_done]);
    FILTER(
    ALLSELECTED('excel-planning production_progress'[last_change]);
    ISONORAFTER('excel-planning production_progress'[last_change]; MAX('excel-planning production_progress'[last_change]); DESC)
    )
    )

     

    This formula provides me with the same results (problems) as described before.

     

    Thanks

  • Greg_Deckler's avatar
    Greg_Deckler
    8 years ago

    When I recreated this, I got the correct results.

     

    boxes_done running total in last_change = 
CALCULATE(
SUM('Boxes'[boxes_done]),
FILTER(
ALLSELECTED('Boxes'[last_change]),
ISONORAFTER('Boxes'[last_change], MAX('Boxes'[last_change]), DESC)
)
)

    Here is my Enter Data query, make sure that your last_change is a Date/Time column and not text or something.

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jdDRCcAgDATQVUq+C+aS2KoTdAdx/zWqFhT60QpHkMDj1JwJgU2Edrr0OMWhHeGkhhE2aPKaWNtSYp9U9j8l6CoOZbaiehd4duFLyaP8q0p1AbVnxSQ2LxhWlCWEmqHqX5Qb", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [ordernr = _t, articlenr = _t, last_change = _t, boxes_amount = _t, boxes_done = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ordernr", Int64.Type}, {"articlenr", type text}, {"last_change", type datetime}, {"boxes_amount", Int64.Type}, {"boxes_done", Int64.Type}})
in
    #"Changed Type"

     

     

8 Replies

    • vincentpit's avatar
      vincentpit
      Frequent Visitor
      8 years ago

      Hi Smoupre, thanks for the quick response!

       

      I've tried quick measure, which gives me the following formula:

      cumulative =
      CALCULATE(
      SUM('excel-planning production_progress'[boxes_done]);
      FILTER(
      ALLSELECTED('excel-planning production_progress'[last_change]);
      ISONORAFTER('excel-planning production_progress'[last_change]; MAX('excel-planning production_progress'[last_change]); DESC)
      )
      )

       

      This formula provides me with the same results (problems) as described before.

       

      Thanks

      • vincentpit's avatar
        vincentpit
        Frequent Visitor
        8 years ago

        Hi Smoupre,

        The measure works, thanks :) . Only now I encounter another problem.
        I have to filter on ordernumber, if I unfilter, it takes forever to load the visual. If I delete the measure everything works fine. Do you have any tips that we can use in order to make the visual work smoothly?

        I read something about a poorly written measure, but since we used quick measure I think that would be odd.

        Sorry for the inconvenience.

        Vincent

         

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    8 years ago

    When I recreated this, I got the correct results.

     

    boxes_done running total in last_change = 
CALCULATE(
SUM('Boxes'[boxes_done]),
FILTER(
ALLSELECTED('Boxes'[last_change]),
ISONORAFTER('Boxes'[last_change], MAX('Boxes'[last_change]), DESC)
)
)

    Here is my Enter Data query, make sure that your last_change is a Date/Time column and not text or something.

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jdDRCcAgDATQVUq+C+aS2KoTdAdx/zWqFhT60QpHkMDj1JwJgU2Edrr0OMWhHeGkhhE2aPKaWNtSYp9U9j8l6CoOZbaiehd4duFLyaP8q0p1AbVnxSQ2LxhWlCWEmqHqX5Qb", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [ordernr = _t, articlenr = _t, last_change = _t, boxes_amount = _t, boxes_done = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ordernr", Int64.Type}, {"articlenr", type text}, {"last_change", type datetime}, {"boxes_amount", Int64.Type}, {"boxes_done", Int64.Type}})
in
    #"Changed Type"