Unique Values

[Meiko]

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

[Bifinity_75 · Accepted solution]

Hi @Meiko , 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

[Meiko]

Thanks for your help. I tried your formula but still the same result for November and December (please see below):

[Meiko]

There was an issue with my Index. I had to sort by Name and Date first, then create the Index. it works now.

Thanks again