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.
Some of the users are not able to see the pages from a report.
Hello, I have a report which was working fine to all the users of the report. But now some of the things are not loading in the report giving an error as Coudent load the data for this visual. Which is working fine for some of the users. all the access are given by rls to the users. If looking by the viewer of the rls in desktop or service its not loading the visual. Can anyone help me out in this?
Calculate Last Transaction Records
Hi, Within Power BI, I have a below table and I want to create another column which calculates Last Margin % for every records. If the customer A buys product A12 then for its Last Margin % is null and if same customer (i.e. A) buys same product (i.e. A12) then its Margin % will 30%. So my result table will be as below. I want to write DAX for this. Can some one help me on this?
