sumx and datediff with remaining days
Hi everyone, I have an issue where im trying to count the number of days between two days that is determined by the year chosen. What i want is to have remaining days between start and end date being summed up in the year chosen in the filter context. For example: I have no problem in counting the dates (as shown), but im getting issues as soon as i am trying to put it into a sumx. The dates and datesdiff isnt' totalling correctly when the filter context is different from the date incurred (which the filter is connected to). The measure im using is as follows: Diff_acrossDates = VAR min_data = MIN ( 'Data'[Date incurred] ) VAR max_data = MAX('Data'[Date paid]) VAR max_data_relat = CALCULATE(MAX('Data'[Date paid]), USERELATIONSHIP(Betalt_Kalender[DateKey], 'Data'[Date paid])) VAR valgt_dato = IF(ISFILTERED('Betalt_Kalender'[Year]), DATE(SELECTEDVALUE(Betalt_Kalender[DateKey]), 12, 31)) VAR valgt_dato2 = SELECTEDVALUE('Betalt_Kalender'[Year]) VAR dato_01_01 = IF(ISFILTERED('Betalt_Kalender'[Year]), DATE(MIN('Betalt_Kalender'[Year]), 01, 01)) VAR dato_12_31 = IF(ISFILTERED('Betalt_Kalender'[Year]), DATE(MAX('Betalt_Kalender'[Year]), 12, 31)) RETURN sumx('Data', IF( min_data < dato_01_01 && max_data_relat >= dato_01_01, DATEDIFF(dato_01_01, max_data_relat, DAY) + 1 , //counting down from date paid IF( min_data >= dato_01_01 && max_data_relat <= dato_01_01, DATEDIFF (MIN('Data'[Date incurred]), dato_12_31, DAY) + 1 //counting up from date incurred , IF( min_data >= dato_01_01 && max_data_relat <= dato_12_31, DATEDIFF(min_data, max_data, DAY) + 1 //counts between dates , 0) ) ) ) please let me know if further info is needed
