Probleme when Evaluating dates

[PatrickByGecko]

Hello

I get a problem when comparing to dates, Is this instruction correct?

VAR _DateBeginMonth = DATEVALUE("01/" & MONTH(TODAY()) & "/" & YEAR(TODAY()))

VAR _DateBeginFinal = if ( 'TABLE'[DATE BEGIN] <= _DateDebutMonth ; _DateDebutMonth; 'TABLE'[DATE BEGIN] )

Because this comparison is always false though I'm sure 'TABLE'[DATE BEGIN] is bigger than _DateDebutMonth.

So I suppose I have a problem of syntax...

[mwegener]

Hi @PatrickByGecko ,

try DATE instead of DATEVALUE.

https://docs.microsoft.com/en-us/dax/date-function-dax

[PatrickByGecko]

I did this way

VAR _DateBeginMonth = DATE( YEAR(TODAY()); MONTH(TODAY()) ; 1)

But the comparison gives the same, is always false though I'm sure 'TABLE'[DATE BEGIN] is bigger.

[mwegener]

Hi @PatrickByGecko ,

your formula 'TABLE'[DATE BEGIN] <= _DateDebutMonth says 'TABLE'[DATE BEGIN] is less or equal to _DateDebutMonth and if 'TABLE'[DATE BEGIN] is bigger than _DateDebutMonth this is false.

[PatrickByGecko]

You are right, the problem is somewhere else underneath =>

VAR _DateBeginMonth = DATE( YEAR(TODAY()); MONTH(TODAY()) ; 1)
VAR DateEndMonth = DATE( YEAR(TODAY()); MONTH(TODAY()) ; 30)
VAR _DateBeginAbsence = if ( 'TABLE'[DATE BEGIN] <= _DateBeginMonth ; _DateBeginMonth; 'TABLE'[DATE BEGIN])
VAR _DateEndAbsence = if ( 'TABLE'[DATE END] > DateEndMonth ; DateEndMonth; 'TABLE'[DATE END])

VAR _NbrDayAbsenceMaladie = SUMX(FILTER('TABLE';'TABLE'[COLLABORATEURId]=EARLIER('TABLE'[COLLABORATEURId]) && ('TABLE'[TYPE COLLAB] = "CDI" || 'TABLE'[TYPE COLLAB] = "CDD") && 'TABLE'[CODE PROJET] = "MALADIE01" ); DATEDIFF(_DateBeginAbsence;_DateEndAbsence;day)+1)
VAR _NbrDayAbsenceChomage = SUMX(FILTER('TABLE';'TABLE'[COLLABORATEURId]=EARLIER('TABLE'[COLLABORATEURId]) && ('TABLE'[TYPE COLLAB] = "CDI" || 'TABLE'[TYPE COLLAB] = "CDD") && 'TABLE'[CODE PROJET] = "CHOMAGE01" ); DATEDIFF(_DateBeginAbsence;_DateEndAbsence;day)+1)

The problem is that my variables can't be taken in account in the FILTER function only for the lines of the tables where there is "MALADIE01" and "CHOMAGE01" lines. The DATEDIFF function changes for each line of my table that I don't want.

Note that the FILTER works if I replace the variables VAR_ by the the real names of the column => DATEDIFF(TABLE'[DATE BEGIN];'TABLE'[DATE END];day)+1). Only "MALADIE01" and "CHOMAGE01" lines are treated for the DateDiff.

But I loose my two conditions in green..

😞

[mwegener]

Hi @PatrickByGecko,

i think you have to use the DAX variables inside the iterator SUMX...

VAR _DateBeginMonth = DATE( YEAR(TODAY()); MONTH(TODAY()) ; 1)
VAR DateEndMonth = DATE( YEAR(TODAY()); MONTH(TODAY()) ; 30)

VAR _NbrDayAbsenceMaladie = SUMX( FILTER('TABLE';'TABLE'[COLLABORATEURId]=EARLIER('TABLE'[COLLABORATEURId]) && ('TABLE'[TYPE COLLAB] = "CDI" || 'TABLE'[TYPE COLLAB] = "CDD") && 'TABLE'[CODE PROJET] = "MALADIE01" );

VAR _DateBeginAbsence = if ( 'TABLE'[DATE BEGIN] <= _DateBeginMonth ; _DateBeginMonth; 'TABLE'[DATE BEGIN])
VAR _DateEndAbsence = if ( 'TABLE'[DATE END] > DateEndMonth ; DateEndMonth; 'TABLE'[DATE END])

RETURN

DATEDIFF(_DateBeginAbsence;_DateEndAbsence;day)+1)
VAR _NbrDayAbsenceChomage = SUMX( FILTER('TABLE';'TABLE'[COLLABORATEURId]=EARLIER('TABLE'[COLLABORATEURId]) && ('TABLE'[TYPE COLLAB] = "CDI" || 'TABLE'[TYPE COLLAB] = "CDD") && 'TABLE'[CODE PROJET] = "CHOMAGE01" );

VAR _DateBeginAbsence = if ( 'TABLE'[DATE BEGIN] <= _DateBeginMonth ; _DateBeginMonth; 'TABLE'[DATE BEGIN])
VAR _DateEndAbsence = if ( 'TABLE'[DATE END] > DateEndMonth ; DateEndMonth; 'TABLE'[DATE END])

RETURN

DATEDIFF(_DateBeginAbsence;_DateEndAbsence;day)+1)

https://www.kasperonbi.com/using-dax-variables-in-iterators/

[PatrickByGecko · Accepted solution]

Hi

I did put my conditions inside the FIlter command and it is OK. It is not a beautiful code but it works...

Thank you again.

[PatrickByGecko]

When I display the values so as to check

VAR _DateBeginMonth = 43922 and 'TABLE'[DATE BEGIN] = 43942. So the problem must coming from the syntax of comparison. I think..

[mwegener]

Okay, these are the dates you are comparing