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 ,

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 !