DAX Date help

[johnce123]

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 return availability of 366 Days

However a start date may be entered as 01/04/2020

End date 31/12/2099

This would need to return 275 Days. 

Help appreciated

[camargos88 · Accepted solution]

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

[AllisonKennedy]

@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]

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

[camargos88 · Accepted solution]

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

[johnce123]

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 · Accepted solution]

@johnce123 ,

Try this measure:

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

[ChrisMendoza]

@johnce123 - You can use DATEDIFF like:

Measure Remaining Days = 
VAR beginDate = DATE(2020,4,1)
RETURN
DATEDIFF(beginDate,MAX('Table'[Date]),DAY )