distribute values in specific date range

[junyetzotomayor]

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
end date: may 2017
target: 17,600

I want to distribute that target to for those date range. So I should see 40,600 in April (23,000 + 17,600)

Thanks in advance!

[Phil_Seamark]

Hi @junyetzotomayor

Where does the 23,000 come from?  Is that Record 1?

[junyetzotomayor]

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]

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]

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]

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]

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]

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)

[Phil_Seamark]

Do you have a relationship between NPS and your DATE table?

[junyetzotomayor]

Hi @Phil_Seamark

Sorry might have misunderstood it. 

Hmm.. How would I related my 2 fields in NPS (Start Date and End Date) to Date(date field) wherein Date table has only one field. Do you have any idea?

Thanks again!

[junyetzotomayor]

Hi @Phil_Seamark

Yes already have. I connected [Date]'Date' to [NPS and Sales Target Capture]'Date'.

[Phil_Seamark]

Hmmm so this measure should in theory work

My Measure = CALCULATE (
    SUM ( 'NPS Sales and Target Capture'[Total Annual Target TEU] ),
    FILTER (
        ALL ( 'Date'[Date] ),
        'Date'[Date] <= MAX ( 'Table'[Date] )
    )
)

Try adding the DATE column from your DATE table to the axis rather than the date column from NPS Sales and Target Capture

[junyetzotomayor]

Hi @Phil_Seamark

Sorry might have misunderstood it. I don't think I have the relationship yet between NPS and Date table.

Hmm.. How would I related my 2 fields in NPS (Start Date and End Date) to Date(date field) wherein Date table has only one field. Do you have any idea?

Thanks again!

[Phil_Seamark]

Make two relationships.

The first will be between StartDate on the NPS table and Date on the Date table.  This relationship will be a solid line in the relationship diagram,

Then if you need a second relationship between the two tables, create it between the enddate on the NPS table and the Date table.  This will be a dotted line, or an inactive relationship.  This can be used in measures, but only specifically when mentioned.  You may not need this at all/

[junyetzotomayor]

Hi @Phil_Seamark

Awesome it worked! Thank you!

However, the only catch is, we can only set one relationship (active) for between two tables. For Start Date and End Date, the only active one is the 'Start Date' hence when I filter my date range, other filters in my report is not working properly. Do you have any thoughts on this?

Example:
Start Dates:
Jan. 31, 2017
Feb. 4, 2017

End Date:
June 13, 2017
May 24, 2017

When I select the filter to show March onwards(which is outside Start Date) records, the aggregation works properly but not the other filters. See screenshot.

[Phil_Seamark]

I'm heading out for the rest of the day but will try to have a look at this for you later tonight.

[junyetzotomayor]

Thanks @Phil_Seamark! Appreciate it!

[Phil_Seamark]

Hi @junyetzotomayor,

Any chance you can send me a cut down version of your PBIX file?  Feel free to private message me the link. This will make it much easier for me to figure out.  🙂

Cheers,

Phil

[GilbertQ]

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]

Hi @GilbertQ

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

Thank you!

[GilbertQ]

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.