sumx and datediff with remaining days

[LasseLJørgensen]

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

[some_bih]

Hi @LasseLJørgensen the best practice will be to put two dates in column and in third column find simple difference like INT(date1)-INT(date2)

Still, if you need slicer affected difference, when one possible option is usage of SELECTEDVALUE function.

There is no model / details from your side for input and expected ouput to provide some solution.

[LasseLJørgensen]

@some_bih 
Hi there, im allready using a date table.

What im trying to do is count a datediff where the start and end up until 01-01 and 12-31 so that it counts the datediff in eg 2014 or 2015. the difference in dates has to 'fit' the year the start and end is located.

[some_bih]

Hi @LasseLJørgensen I do not fully understand what you are need to get, but what I understand is that difference for some dates, best work with dates from Calendar / Date table. My practice when I need to calculate some difference for dates, min or max, I usually calculate some event date compared with Calendar / Date table date and put visual with this column. This is also best practice as unique key column is guaranteed compared with possible duplicated rows is eventually fact (your table). This could be sometimes trouble as context transition (usage of DAX function CALCULATE) perform differently on dimension compared to fact tables.

If possible, try to remodel your request from / toward Calendar / Date table.