Forum Discussion

junyetzotomayor's avatar
junyetzotomayor
Helper I
9 years ago

distribute values in specific date range

Is it possible in PBI to distribute values in specific date range? Lets say I have the following records (see screenshot): Record 1 start date: january 2017 Record 2 start date: feb 2017 en...
GilbertQ's avatar
GilbertQ
Super User
9 years ago

Hi junyetzotomayor

 

It sounds to me like you are looking to do a Cumulative/Running total?

 

If so you can use the following syntax?

 

CALCULATE (
    SUM ( 'Table'[Amount] ),
    FILTER (
        ALL ( 'Date'[Date] ),
        'Date'[Date] <= MAX ( 'Table'[Date] )
    )
)

 

  • junyetzotomayor's avatar
    junyetzotomayor
    Helper I
    9 years ago

    Hi GilbertQ

     

    Sorry I'm new to PowerBI. Is that syntax a new column?

     

    Thank you!

    • GilbertQ's avatar
      GilbertQ
      Super User
      9 years ago

      Hi junyetzotomayor,

       

      Ok if you are new, then what you would require is a Date Table, and here is a great blog post explaining.

       

      Do You Need a Date Dimension?

       

      Then what you need to do is once that is created, is to create a New Measure, with the Syntax I provided previously.

      Please let us know if you get stuck. 

      • junyetzotomayor's avatar
        junyetzotomayor
        Helper I
        9 years ago

        Hi GilbertQ

         

        So I manage to create a separate table based on the date values of my [Table]. Then, I created a Measure based from the syntax you mentioned.

         

        After this I tried dragging the 'Measure' to my line chart in the values and it seems it still not adding up. 

        Did I miss anything?

         

        Again, thank you for the help :)