How to do VLOOKUP in my PowerBI table

Question

Gentlemen, in my table I have a column CC (Number of Contracts), and another column "DATE" with the dates of each contract, but I want to make a comparison (Something like a PROCV in Excel), where I check in my column CC if have the same contract numbers as the previous days or no. This way I can know exactly if there were new hires or if there were any terminated contracts. That is, what I need is a DAX formula or some mechanism that can be used to carry out this verification, such as an "IF", IF in my CC column there is a contract for more than the day before, insert it in the column "NEW CONTRACT" , otherwise "CLOSED".

Image below of my table:

Answer 1

To compare the number of contracts in one day to the number of contracts on the previous day in PowerBI, you can use the CALCULATE and FILTER functions together in a calculated column. Here's an example of how you can use these functions to create a calculated column that shows whether the number of contracts on a given day is the same as the previous day:

1. Open your PowerBI report and select the table you want to add the calculated column to.
2. Click the Modeling tab in the ribbon, and then click the New Column button.
3. In the formula bar, type the following formula to create a calculated column:

  =CALCULATE(
       COUNT(CC[Number of Contracts]),
       FILTER(
           ALL(CC),
           CC[Date] = MAX(CC[Date]) - 1
       )
   )

The formula above uses the CALCULATE function to count the number of contracts on the previous day, and uses the FILTER function to select only the rows with the previous day's date.

Once you've created the calculated column, you can use an IF statement to compare the number of contracts on the current day to the number of contracts on the previous day, and show "NEW CONTRACT" or "CLOSED" in the resulting column. Here's an example of how you can do this:

  =IF(
        [Number of Contracts] = [Number of Contracts (Previous Day)],
        "CLOSED",
        "NEW CONTRACT"
   )

The formula above used the IF function to compare the values in the Number of Contracts column to the values in the Number of Contracts (Previous Day) column. If the values are the same, it shows "CLOSED" in the resulting column. If the values are different, it shows "NEW CONTRACT" in the resulting column.

I hope it helps!!