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.
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
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:
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 )
Then divide the fee amount by this difference (Final = Fee/ 5 months)
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.)
Calculate the months between start and end date with DATEDIFF.
Thank you for all your help. I enclose a mock up of the data.
I really appreciate all help and guidance.
Basically I need:
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.
Then divide the fee amount by this difference (Final = Fee/ 5 months) - Until i don’t get the datediff i can’t get this.
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.
Have had a look at the model and it doesn’t seem you’ve gone through and used the techniques advised above?
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
