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())))
)

     

     

     

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

johnce123 Do you have a DimDate table? What format is your data in (ie how are the start and end dates entered and in what table and related to DimDate table?)?  You could use: 
Days In Year = 

VAR _StartDate = IF(YEAR(Table[StartDate]) <YEAR(TODAY()), DATE(YEAR(TODAY()), 1, 1), Table[StartDate])

VAR _EndDate = IF(YEAR(Table[EndDate]) > YEAR(TODAY()), DATE(YEAR(TODAY()), 12, 31), Table[EndDate])

RETURN
CALCULATE(COUNTROWS(ALL(DimDate)), DATESBETWEEN(_StartDate, _EndDate))

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

    Hi, it's a direct query into an SQL database which is my live management system (business central) and returns a date along with a time. 

    Thanks for your help