Forum Discussion

johnce123's avatar
johnce123
Icon for Helper I rankHelper I
5 years ago
Solved

DAX Date help

I have a situation where i want to count the number of days available in the current year.  There is a start date column of say 01/01/2000  End date column of say 31/12/2099   This would need to ...
Need Help
  • camargos88's avatar
    camargos88
    5 years ago

    johnce123 ,

     

    You can use this measure:

    _QtDays = 
SUMX('Table',
        IF(
            YEAR('Table'[StartDate]) <> YEAR(TODAY()), 
            DATEDIFF(DATE(YEAR(TODAY()), 1,1), DATE(YEAR(TODAY()), 12,31), DAY) + 1,
            DATEDIFF('Table'[StartDate], DATE(YEAR('Table'[StartDate]), 12,31), DAY) + 1
        ))

     

  • camargos88's avatar
    camargos88
    5 years ago

    johnce123 ,

     

    Try this measure:

     

    _QtDays = 
SUMX(
    'Table',
    COUNTROWS(FILTER(CALENDAR('Table'[StartDate], 'Table'[EndDate]), YEAR([Date]) = YEAR(TODAY())))
)

     

     

     

johnce123's avatar
johnce123
Icon for Helper I rankHelper I
5 years ago

Would this also take into account if the end date fell before the end of the current year 

 

so if my end date was 31/08/2020 it would calculate from the start date or beginning of year to the 31/08/2020?

camargos88's avatar
camargos88
Icon for Community Champion rankCommunity Champion
5 years ago

johnce123 ,

 

Try this measure:

 

_QtDays = 
SUMX(
    'Table',
    COUNTROWS(FILTER(CALENDAR('Table'[StartDate], 'Table'[EndDate]), YEAR([Date]) = YEAR(TODAY())))
)