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.RANK indexing with condition
Hi, have been looking at indexing again, and whether some sequencing conditions would be achievable. For (see snip below): RAND = RAND() RANK = RANKX(ALL(Table),Table[RAND]) RANK INC = IF(Table[isCurrent]="No",RANKX(ALL(Table),Table[RAND])) Is it possible to in the RANK INC not have it skip the excluded numbers and keep the sequence running without number gaps. Thought about && Table[isCurrent]="No" in RANK but can't get it right. Or is RANKX not going to be the best option to fulfil? Separately, I was then working on 3 digits to make up the number in RANK INC to be a full 3 digit sequence with leading zeroes. Still learning switch but would switch enable length decision and prefix 00 (for 1), 0 (for 2). Thanks,
Issue with Conditional Formatting in Matrix Visual
Hi Team, Hope you're all doing well. I was working on applying conditional formatting in the Matrix visual, where I have a column called "ppk" and I am applying the following criteria to achieve color coding using DAX. Using this DAX (with averages included): DAX PPK_Conditional_Color_heatmap = VAR Ppk_Value = AVERAGE('PPK'[ppk_vl]) // No COALESCE applied VAR StdDev_Value = AVERAGE('PPK'[stddev_vl]) // No COALESCE applied RETURN SWITCH( TRUE(), Ppk_Value = 0 && StdDev_Value = 0, "#808080", // Grey for NULL Ppk & StdDev is Blank Ppk_Value = 0 && StdDev_Value = 0, "#ADD8E6", // Light Blue for NULL Ppk & StdDev = 0 Ppk_Value >= 1.33, "#69b764", // Green Ppk_Value >= 1.00 && Ppk_Value < 1.33, "#ffc156", // Yellow Ppk_Value < 1.00, "#d82526" // Red ) The criteria for coloring are as follows: Ppk >= 1.33 → Green Ppk >= 1.00 && Avg_Ppk < 1.33 → Yellow Ppk < 1.00 → Red PPK = NULL & StdDev_Vl = 0 → Light Blue PPK = NULL & StdDev_Vl = Blank → Grey However, after applying the formula, the grey and light blue colors are not showing as expected. I believe the issue is that when the cell is empty, the conditional formatting is not triggering. This might be because blank values aren’t being accounted for correctly in the DAX expression. Has anyone encountered this issue, or do you have any suggestions on how to address it? Looking forward to your inputs. Thanks & Regards, Hk
How to debug DAX measure that result in table
Hello, As the subject says, am wondering if there's a way to know the result/output of a measure that resulted in a table? For example, I have a measure below, as you can see, I have _table1. If I want to break down the formula and see the output, from FILTER function, all the way to the AddColumns, is there a way to do it? Thanks MEASURE = VAR _obj = SELECTEDVALUE ( 'Table'[obj code] ) VAR _cost = SELECTEDVALUE ( 'Table'[cost code] ) VAR _id = SELECTEDVALUE ( 'Table'[id] ) VAR _table1 = ADDCOLUMNS ( 'tbl_Tier1', "c1", SUMX ( FILTER ( mirrorFactTableWithAllCostCodes, [id] = EARLIER ( tbl_Tier1[T1_From] ) && [obj code] = _obj && [cost code] = _cost ), [Column] ) * [T1_Alloc] ) VAR _table2 = ADDCOLUMNS ( 'tbl_Tier2', "c1", VAR _result = ( SUMX ( FILTER ( _table1, [T1_To] = EARLIER ( tbl_Tier2[T2_From] ) ), [c1] ) + SUMX ( FILTER ( 'mirrorFactTableWithAllCostCodes', [id] = EARLIER ( tbl_Tier2[T2_From] ) && [obj code] = _obj && [cost code] = _cost ), [Column] ) ) * [T2_Alloc] RETURN IF ( ISBLANK ( _result ), 0, _result ) ) VAR _table3 = ADDCOLUMNS ( 'tbl_Tier3', "c1", VAR _result = ( SUMX ( FILTER ( _table2, [T2_To] = EARLIER ( tbl_Tier3[T3_From] ) ), [c1] ) + SUMX ( FILTER ( 'mirrorFactTableWithAllCostCodes', [id] = EARLIER ( tbl_Tier3[T3_From] ) && [obj code] = _obj && [cost code] = _cost ), [Column] ) ) * [T3_Alloc] RETURN IF ( ISBLANK ( _result ), 0, _result ) ) RETURN SUMX ( FILTER ( _table3, [T3_To] = _id ), [c1] ) + SUMX ( FILTER ( 'mirrorFactTableWithAllCostCodes', [id] = _id && [cost code] = _cost && [obj code] = _obj ), [Column] )
