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...
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] )
    )
)
    • Phil_Seamark's avatar
      Phil_Seamark
      Microsoft Employee
      9 years ago

      Oh junyetzotomayor, if that doesn't work, can you please share a sample of your data table (including the name of the table) so we can give you a more precise formula.

      • junyetzotomayor's avatar
        junyetzotomayor
        Helper I
        9 years ago

        Hi Phil_Seamark

         

        Thanks for the reply.

         

        I followed your steps but for some reason it still didnt work :(

         

        Please see screenshot of my sample data table.

         

        Main Table: NPS and Sales Target Capture

                -2 tables are appended (NPS and Sales Target Capture)

                - Im only interested to calculate the cumulative TEU of Sales Target Capture as they are the ones with 'Start Date' and 'End Date' as seen on screenshot below.

         

        Date Table: Created a CALENDAR() based from the min and max values of Sales Target Captur (see screenshot)