Time series view for current and future dates

[Anonymous]

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

[Anonymous]

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

[Anonymous]

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

[Anonymous]

Hi @Anonymous ,

Did you get a chance to take a look at this?

Any inputs or suggestions on tackling this would be helpful.

Thanks,

Vishy

[Anonymous]

Hi @Anonymous ,

Sorry for late. First, you can create a date dimension table with the following formula to get dynamic date axies:

Calendar = CALENDAR(TODAY(),EOMONTH(TODAY(),3))

Then create a measure to get the percentage, and drag the date field in date dimension table and measure to line chart:

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]),'Table'[LWD]>curdate)))
return DIVIDE(billedresource,totalresource)​

I just prepare one smaple reprt file for you, you can check it in this link.

Best Regards

Rena

[Anonymous]

Hi @Anonymous ,

Thanks for the detailed steps.

However, when I verify the results, it is incorrect.

Example - For 17th May, there are total 114 resources out of which 105 are billable which should make the resource utilization percentage as 105/114 which is around 92%. 

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]),'Table'[LWD]>curdate)))
return DIVIDE(billedresource,totalresource)

In your above calulation of var billedresource, shouldn't the condition on End Date be End Date>curdate instead of < ? I tried modifying that but still the calculation does not gvie the correct result.

Regards,

Vishy

[Anonymous · Accepted solution]

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 )
                )
        )
    )
RETURN

    DIVIDE ( 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

[Anonymous]

Thank you @Anonymous !