Forum Discussion

BonnieTseng's avatar
BonnieTseng
New Member
4 years ago
Solved

How to calculate working days

I have two tables.

The one is user call data (including start time and end time )

 

The anthoer one is calendar (including working day)

 

how can i calculate user call's data that how many days in Start time & End time. thank you

Need Help
  • Jihwan_Kim's avatar
    Jihwan_Kim
    4 years ago

    Hi,

    I am not sure how your expected outcome looks like, but please check the below picture and the attached pbix file.

    I tried to create a sample pbix file like below.

     

     

    Count working days measure: =
IF (
    HASONEVALUE ( Data[ID] ),
    COUNTROWS (
        FILTER (
            'Calendar',
            'Calendar'[Working day] = "Working day"
                && 'Calendar'[Date] >= MAX ( Data[Startdate] )
                && 'Calendar'[Date] <= MAX ( Data[Enddate] )
        )
    )
)

3 Replies

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Icon for Super User rankSuper User
    4 years ago

    Hi,

    I am not sure how your expected outcome looks like, but please check the below picture and the attached pbix file.

    I tried to create a sample pbix file like below.

     

     

    Count working days measure: =
IF (
    HASONEVALUE ( Data[ID] ),
    COUNTROWS (
        FILTER (
            'Calendar',
            'Calendar'[Working day] = "Working day"
                && 'Calendar'[Date] >= MAX ( Data[Startdate] )
                && 'Calendar'[Date] <= MAX ( Data[Enddate] )
        )
    )
)
  • rajulshah's avatar
    rajulshah
    Icon for Resident Rockstar rankResident Rockstar
    4 years ago

    Hello BonnieTseng ,

     

    Can you please provide the sample file removing confidential information? It would be easier solving that way.