Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

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 d...
Need Help
Show and Tell
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    6 years ago

    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's avatar
Anonymous
Not applicable
6 years ago

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's avatar
Anonymous
Not applicable
6 years ago

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's avatar
    Anonymous
    Not applicable
    6 years ago

    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's avatar
      Anonymous
      Not applicable
      6 years ago

      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's avatar
        Anonymous
        Not applicable
        6 years ago

        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