DAX and leap year, date range
Been researching this a while. Its not the usual Year on Year, and it not finding a corresponding 29th Feb but the period of a date range calculated. I am trying to bring in a count to then subtract that from the total. I've found something useful via Colin Maitland from 4 years ago but having problems finalising it. Code is: Its the removefilters & datesbetween having difficulty with. Even if I resolved that I'm not sure it will provide the solution so was looking for any other ideas. Thanking you calc = VAR _MAX_DATE = MAX ( 'Date'[Date], ) VAR _DAYS_IN_YEAR = IF ( NOT ISBLANK ( _MAX_DATE ), SWITCH ( TRUE(), // Is Max Date Year a Leap Year? DATEDIFF ( DATE ( YEAR ( _MAX_DATE ), 02, 28 ), DATE ( YEAR ( _MAX_DATE ), 03, 01 ), DAY ) = 2, IF ( _MAX_DATE >= DATE ( YEAR ( _MAX_DATE ), 02, 29 ), // NOTE: Compare with 29/02 of the highest selected Year here. 366, // Include 365 Days plus 29th February in the highest selected Year. 365 ), // Is Max Date Previous Year a Leap Year? DATEDIFF ( DATE ( YEAR ( _MAX_DATE ) - 1, 02, 28 ), DATE ( YEAR ( _MAX_DATE ) - 1, 03, 01 ), DAY ) = 2, IF ( _MAX_DATE <= DATE ( YEAR ( _MAX_DATE ), 02, 28 ), // NOTE: Compare with 28/02 of the highest selected Year here. 366, // Include 365 Days plus 29th February in the previous Year to the highest selected Year. 365 ), // Not a Leap Year 365 ) ) VAR _MIN_DATE = IF ( NOT ISBLANK ( _MAX_DATE ), ( _MAX_DATE - _DAYS_IN_YEAR ) + 1 // Adjust by one Day to ensure that same date as Max Date from previous Year is not included. ) VAR _RESULT = IF ( NOT ISBLANK ( _MAX_DATE ), CALCULATE ( SELECTEDMEASURE(), REMOVEFILTERS ( 'Date Range' ), DATESBETWEEN ( 'Date'[Date], _MIN_DATE, _MAX_DATE ) ) ) RETURN _RESULT //In this example, if there was no need to ensure that 365 days plus the 29th of February needed to be included in this calculation, then the VAR _DAYS_IN_YEAR = part of the formula could simply be changed to VAR _DAYS_IN_YEAR = 365.
