Forum Discussion
Unique Values
Hi all,
I have a table with multiple columns. One is Resource Name, one is Date, one is Absences. What I need to do is for each Month (i.e. a record under the Date column) if that Date is in future I calculate vacations, if not I use the number under Absences column. I created the a column and named it Vacations which is using the following DAX:
Vacations =
VAR CurrentDate =DATE ( YEAR ( TODAY () ), MONTH ( TODAY () ), 01 )
RETURN
IF ( ResPlan[Date] < CurrentDate, ResPlan[Absences],
IF (MONTH ( ResPlan[Date] ) = 7 || MONTH ( ResPlan[Date] ) = 8, 5 * 7,
IF ( MONTH ( ResPlan[Date] ) = 12, 4 * 7, 3 * 7 )))
The problem I having is when the Date is in future (for example for 01,November, 2022 and 01, December 2022)
I get mulitple values under Vacations. I only need one value per Date (please see the attached file for details).
I also created an index column but could not figure out how to use it to get only one value under Vacations.
Thanks,Unique Value
Hi Anonymous , try this:
1.- Inser a Index Column in Power Query (in my example the name of column is Índex2
2.- Create a calculate column with this formule:
Vacations = VAR CurrentDate =DATE ( YEAR ( TODAY () ), MONTH ( TODAY () ), 01 ) VAR Condi=if(ResPlan[Date]=LOOKUPVALUE(ResPlan[Date],ResPlan[Índex2],ResPlan[Índex2]-1),1,0) RETURN IF ( ResPlan[Date] < CurrentDate, ResPlan[Absences], IF (Condi=0, IF (MONTH ( ResPlan[Date] ) = 7 || MONTH ( ResPlan[Date] ) = 8, 5 * 7, IF ( MONTH ( ResPlan[Date] ) = 12, 4 * 7, 3*7)), BLANK()) )The result:
Best regards
3 Replies
Hi Anonymous , try this:
1.- Inser a Index Column in Power Query (in my example the name of column is Índex2
2.- Create a calculate column with this formule:
Vacations = VAR CurrentDate =DATE ( YEAR ( TODAY () ), MONTH ( TODAY () ), 01 ) VAR Condi=if(ResPlan[Date]=LOOKUPVALUE(ResPlan[Date],ResPlan[Índex2],ResPlan[Índex2]-1),1,0) RETURN IF ( ResPlan[Date] < CurrentDate, ResPlan[Absences], IF (Condi=0, IF (MONTH ( ResPlan[Date] ) = 7 || MONTH ( ResPlan[Date] ) = 8, 5 * 7, IF ( MONTH ( ResPlan[Date] ) = 12, 4 * 7, 3*7)), BLANK()) )The result:
Best regards