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...
junyetzotomayor's avatar
junyetzotomayor
Helper I
9 years ago

Hi Phil_Seamark

 

Sorry for some reason my my message was incomplete.

 

Yes thats records 1.

 

Here is the details of my records

Record 1
start date: january 2017
end date: june 2017
target: 23,000

Record 2
start date: feb 2017
end date: may 2017
target: 17,600

  • Phil_Seamark's avatar
    Phil_Seamark
    Microsoft Employee
    9 years ago

    Aha, that makes more sense.  

     

    Do you have a DATE table?  If not, I recommend you add one to your model.  This will make the DAX calucations easier.

     

     

    • junyetzotomayor's avatar
      junyetzotomayor
      Helper I
      9 years ago

      Hi Phil_Seamark

       

      I just created my Date table based from the values of my table. But still can't figure out how would I sum up all the records in date ranges.

       

      Any help would be greatly appreciated. 

       

      Thank you!

      • Phil_Seamark's avatar
        Phil_Seamark
        Microsoft Employee
        9 years ago

        Hi junyetzotomayor

         

        And have you made a relationship between your main table and the Date table?

         

        I reckon if we add a cumulative measure we will be getting close.  It should look something like this, and then add it to your visual

         

        My Measure = CALCULATE (
    SUM ( 'Table'[target] ),
    FILTER (
        ALL ( 'Date'[Date] ),
        'Date'[Date] <= MAX ( 'Table'[Date] )
    )
)