Forum Discussion
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 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
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 ))Try this measure:
_QtDays = SUMX( 'Table', COUNTROWS(FILTER(CALENDAR('Table'[StartDate], 'Table'[EndDate]), YEAR([Date]) = YEAR(TODAY()))) )
6 Replies
johnce123 - You can use DATEDIFF like:
Measure Remaining Days = VAR beginDate = DATE(2020,4,1) RETURN DATEDIFF(beginDate,MAX('Table'[Date]),DAY )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 ))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?
Try this measure:
_QtDays = SUMX( 'Table', COUNTROWS(FILTER(CALENDAR('Table'[StartDate], 'Table'[EndDate]), YEAR([Date]) = YEAR(TODAY()))) )
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))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
