Forum Discussion
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
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!
21 Replies
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] ) ) )Hi junyetzotomayor,
Ok if you are new, then what you would require is a Date Table, and here is a great blog post explaining.
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.
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,600Aha, 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.
