Creating a measure which is spread over time till the end date of a service

[Gjakova]

Hi there, I don't know how to word this properly but I have the following measure that I want to create.

List of requirements:

1. Check if a project is closed and if the end date of the project is on or before SELECTEDVALUE(Calendar[Date])
   
   • I already have this measure (but if you know something good, happy to learn)
   • IF #1 = Yes, then WorkStock = 0
   • IF #1 = No, then WorkStock = Expected Revenue - Realised Revenue
2. So if it is NO, I need the WorkStock measure (Expected - Realised) to be spread over the time till the end date of a service.

So every service has a startdate and (in most cases) an enddate. So my main question is, how do I spread this revenue?

Furthermore, if the service is a subscription (which in most cases has no enddate) then I need to take December 31st of the current year (SELECTEDVALUE(Calendar[Date]).

If the service is a Fixed Fee and SELECTEDVALUE(Calender[Date]) < EndDate (of the service), then WorkStock = 0

I need to visualise this in a line chart based on Date. I have a good star model where my Fact table contains the ServiceID, Date, and some columns like: hours, price, revenue etc.

Thanks in advance for your help!
======================EXTRA INFO=============
Work stock is the amount of work in dollars that we think we still have to do for a service. We would like to allocate this workload to each working day between a selected date and the end date of the shift, ie future dates. I wonder if this is possible at all.

For example: we have calculated that on Service X there is still 10,000 dollars in work stock. The end date of the service is Friday, April 9. That is 10 working days after SELECTEDVALUE(Calendar[Date]). So in a table you get:

[Icey · Accepted solution]

Hi @Gjakova ,

Something missed above. Please check this one.

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Gjakova]

Hi @Icey thanks so much for your help! It seems to work, I was wondering though. Was the SelectedDate Table needed for this, is there a particular reason for why SELECTEDVALUE ('Calendar' [Date]) could not be used? Hope to hear from you so I can understand this measure better.

[Icey]

Hi @Gjakova ,

If we just use "SELECTEDVALUE ('Calendar' [Date])", the right table visual will just show the date selected. You can try it.

Best Regards,

Icey

[Gjakova]

Hi @Icey thanks once again.

I see the result indeed. But since I'll be using this measure in a line graph, were the Axis is based on Calender[Date], could I leave this measure just like it is or do I need to do something else with it? I tried to link the new table to Calendar[Date] (1-1 relationship) but then my measure started to be "cumulative" (kind of), so it basically calculated the measure every day instead of looking at the first date that is selected.

Looking forward to your advice! 🙂

[Icey]

Hi @Gjakova ,

Please check if the attached .pbix gives you the expected results.

Note: 

I don't know how do you calculate the "Realised Revenue", so I just use the Revenue column in my example. Please replace it with your "Realised Revenue".

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.