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 ,

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    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

      Thank you Anonymous !