Forum Discussion
Time series view for current and future dates
Hi,
The sample data is uploaded on the below link -
https://drive.google.com/open?id=1kbBMFlaOiT0KyQ8GmnGGuDFtNv1rJvGp
The data would be generated daily so the report would be needed on a daily basis with the current and future view.
I need to calculate the resource utilization each day along with the future 3 month future view for resource utilization in a time series graph based on three columns - Column C Billed Status, Column E End Date and Column K LWD (Last Working Day).
Resource Utilization is simply calculated based on the Column C "Billed Status". (Resource Utilization Percentage = No. of resources with status 'Billed' / Total no. of resources).
However, the Column E 'End Date' is to be monitored while the resource utilization percentage is calculated. If the end date is 4/30/2020, for example, the particular resource should not be considered as 'Billed' from 5/1/2020 onwards in calculation of Resource Utilization Percentage. This should be seen in advance for all the future dates in the current report.
Also, the LWD needs to be checked as well. For date 5/16/2020, for example, the Total no. of resources would be reduced by 1 as there is a resource name102, who's LWD is known as 5/15/2020.
On each day, a time series view of resource utilization percentage is needed showing the current as well as future dates.
Please help on this.
Thanks,
Vishy
Hi Anonymous ,
Yes, you are right. Please update the formula as below:
Percentage =
VAR curdate =
MAX ( 'Calendar'[Date] )
VAR totalresource =
DISTINCTCOUNT ( 'Table'[Name] )
- CALCULATE (
DISTINCTCOUNT ( 'Table'[Name] ),
FILTER ( 'Table', NOT ( ISBLANK ( 'Table'[LWD] ) ) && 'Table'[LWD] < curdate )
)
VAR billedresource =
CALCULATE (
DISTINCTCOUNT ( 'Table'[Name] ),
FILTER (
'Table',
'Table'[Billed Status] = "Billed"
&& 'Table'[End Date] > curdate
&& OR (
ISBLANK ( 'Table'[LWD] ),
IF ( NOT ( ISBLANK ( 'Table'[LWD] ) ), 'Table'[LWD] > curdate )
)
)
)
RETURNDIVIDE ( billedresource, totalresource )Finally, the count of the available billed resource should be 95(105-9-1=95) according to the logic you provided before.
- 105 is the number of resources which are with billed status
- 9 is the number of resources which End Date is before 17th May
- 1 is the number of resources which LWD is before 17th May
If the returned value still not correct, please provide more details. Thank you.
Best Regards
Rena
7 Replies
Hi Anonymous ,
Please try to create a measure as below:
Percentage = var curdate = SELECTEDVALUE('Calendar'[Date]) var totalresource= DISTINCTCOUNT('Table'[Name])-CALCULATE(DISTINCTCOUNT('Table'[Name]), FILTER('Table',NOT(ISBLANK('Table'[LWD]))&&'Table'[LWD]<curdate)) var billedresource= CALCULATE(DISTINCTCOUNT('Table'[Name]), FILTER('Table','Table'[Billed Status]="Billed" &&'Table'[End Date]<curdate&&OR(ISBLANK('Table'[LWD]),'Table'[LWD]>curdate))) return DIVIDE(billedresource,totalresource)If the above measure is not applicable for your scenario, please correct me and provide your expected result. Thank you.
Best Regards
Rena
Hi Anonymous ,
I need a resource utilization percentage view with date on the axis.
Example - Since this report is run daily, let's say it report is run today 23rd Apr, I need the resource utilization percentage line graph, starting 23rd Apr till next 3 months on the axis. The calculation for resource utilization percentage needs to take into account the Billed Status, End Date and LWD w.r.t to the current day data as data will be provided daily.
Issue is I do not have a Date column in my excel dataset. In your formula, you have a calendar table from what I see, but how do I link the calendar table to a date column from my dataset?
Please guide me if my understanding if wrong. If you can import the data into PBI and show me, would be helpful.
Thanks,
Vishy