Forum Discussion
Probleme when Evaluating dates
- 6 years ago
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.
I did this way
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.
- PatrickByGecko6 years agoHelper V
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..
😞
- mwegener6 years agoMost Valuable Professional
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/
- PatrickByGecko6 years agoHelper V
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.