Spread data over time between srtart and end

Hi Sam,

I hope you’re well.

I wonder if you could kindly guide me towards the correct training sessions on your website.

Basically I am creating some proposals/opportunity analysis.

I have to calculate the spread value between the start and end month and then I need to show that value over these months.

For example, if the spread for proposal xxx is 100 and it’s between Jan 2019 and Oct 2019, then it should show 100 for all months including Jan 2019 and Oct 2019.

Plus I need a way to roll up and aggregate these values, so in jan 2019 all proposals spread added up.

I thought your cohort session was an option but kindly advise if there are others as well that match my requirements.

I thank you in advance for your help.

Hi,

Not sure about the context you are referring to, I am sure it can be done in DAX. I recently created something in Power Query to this extent, see attached example, hope this helps as well.
Paul
image

numbers between dates.xlsx (26.8 KB)

Hi Paul,

Thank you for your help but I need something that is based on measures in PBI, as the data is going to be changing.
Basically I need:

  1. To calculate the number of months between 2 dates, Start date and End date (For example From Jan 2019 to May 2019 the difference is 5 months )
  2. Then divide the fee amount by this difference (Final = Fee/ 5 months)
  3. Then spread the Final resulting from the formula across the start and end date. (Final should appear in all months from Jan2019 to May2019, so it will appear 5 times, once for each month).

Is this something available in Sam’s training or if I can get any help, I would appreciate.
As data is confidential I am unable to share a PBIX, but I believe this is clear.
Do feel free to ask further clarification.
Thanks in advance

Hi,
If you cannot share the pbix make a mock up of your data please.
General hints:
Check out this video on calculations between dates. (The PQ method can be dynamic, given that you could make a duplicate of the fact query, consider the size of the model though.)

  1. Calculate the months between start and end date with DATEDIFF.


Paul
image

Hi Paul,

Thank you for all your help. I enclose a mock up of the data.

I really appreciate all help and guidance.

Basically I need:

  1. To calculate the number of months between 2 dates, Start date and End date (For example From Jan 2019 to May 2019 the difference is 5 months ) - I tried this using datediff with Max on each date, and it’s calculating a Max for all data instead of per row. If i don’t use Max it comes up with an error of no Single value.

  2. Then divide the fee amount by this difference (Final = Fee/ 5 months) - Until i don’t get the datediff i can’t get this.

  3. Then spread the Final resulting from the formula across the start and end date. (Final should appear in all months from Jan2019 to May2019, so it will appear 5 times, once for each month). - My biggest challenge.

TestingData.xlsx (2.6 MB)

Hi,

I managed to get points 1 and 2 done.
Is there anyone who could help me with point 3 please.

Any ideas or guidance would be extremely appreciated.
Thanks

Can you add a demo file of the whole scenario you’re working with, so that we can jump into it and quickly solve this.

It shouldn’t be too difficult, but make sure to have everything you have done up to date.

Thanks
Sam

Hi Sam,

Thank you for your reply.
Enclosed is a PBIX file with the data and an attempt to get this done

testdatat.pbix (499.3 KB)

Many Thanks
Farana

Sorry for the delay on this.

Have had a look at the model and it doesn’t seem you’ve gone through and used the techniques advised above?

image

If you want to spread the data out between start and end dates you need to use these techniques. It’s called the ‘events in progress’ techniques.

I’ve linked it above but will do it again here.

This can be a little tricky sometimes but very doable, you just have to make sure you’re following it exactly, including the inactive relationships in the data model.

Here’s many other examples around this formula techniques

Sam

Sam,

I really appreciate your detailed guidance, I am going to go through these and work out my solution.

You are really doing an amazing job!
Thanks