Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

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 ...
Need Help
  • Bifinity_75's avatar
    Bifinity_75
    3 years ago

    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

Bifinity_75's avatar
Bifinity_75
Solution Sage
3 years ago

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

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

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      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