Forum Discussion

nandukrishnavs's avatar
nandukrishnavs
Community Champion
3 years ago
Solved

Unique days in the specified range

Hi Everyone,

 

I have a table as specified in the below screenshot. I'm trying to calculate total unique days in the date range.

 

In this example, expect output is 30

 

How can we do it in DAX? This calculation needs to happen dynamically and the result will be used in remaining calculations. Hence PowerQuery solution is not suitable. 

 

 

dates
DAX
GENERATESERIES
  • tamerj1's avatar
    tamerj1
    3 years ago

    Hi nandukrishnavs 
    This shall give you the desired result at the total level or in a card visual. Please refer to attached sample file.

    unique days = 
COUNTROWS (
    DISTINCT (
        SELECTCOLUMNS ( 
            GENERATE (
                'Table',
                CALENDAR ( 'Table'[start date], 'Table'[end date] )
            ),
            "@Days", [Date]
        )
    )
)

2 Replies

  • tamerj1's avatar
    tamerj1
    Community Champion
    3 years ago

    Hi nandukrishnavs 
    This shall give you the desired result at the total level or in a card visual. Please refer to attached sample file.

    unique days = 
COUNTROWS (
    DISTINCT (
        SELECTCOLUMNS ( 
            GENERATE (
                'Table',
                CALENDAR ( 'Table'[start date], 'Table'[end date] )
            ),
            "@Days", [Date]
        )
    )
)