Finding required date.

[nalingupta94]

Hi all,

I am fairly new to powerBI, and am still trying to figure out a lot of it's functioning. I am working on generating a report, and one of the requirements is to replace a date. Below is a dummy dataset to provide more context.

In my fact table I have three columns (marked in blue) and based on these three columns, I want to calculate the new column (marked in red)

Checking year	Code	Date		Updated date
2019	01	19/01/2019		19/03/2019
2019	01	19/03/2019		19/03/2019
2019	02	19/08/2019		19/08/2019
2020	01	30/03/2020		30/03/2020
2020	02	10/08/2020		10/08/2020
2020	02	11/11/2020		10/08/2020
2021	01	12/05/2021		29/03/2021
2021	01	29/03/2021		29/03/2021
2021	02	22/08/2021		22/08/2021
2022	01	30/3/2022		30/3/2022
2022	02	19/08/2022		19/08/2022
2022	02	8/12/2022		19/08/2022

The Logic:

From the above table, I want to check if code =01 AND checking year = YEAR(date), then select the date that has month =3.
If code =02 AND checking year = YEAR(date) then select the date with month = 8.


Would appreciate any help available. Also happy to provide further information, so please reach out!

Thanks in advance!

Nalin.

[Ashish_Mathur · Accepted solution]

Hi

This calculated column formula works

Column = if(and(Data[Code]="1",Data[Checking year]=Data[Year]),LOOKUPVALUE(Data[Date],Data[Monnth number],CALCULATE(MIN(Data[Monnth number]),FILTER(Data,Data[Checking year]=EARLIER(Data[Year])&&Data[Code]="1"&&Data[Monnth number]=3)),Data[Checking year],Data[Year],Data[Code],"1"),LOOKUPVALUE(Data[Date],Data[Monnth number],8,Data[Checking year],Data[Year],Data[Code],"2"))

[nalingupta94]

Hi @Ashish_Mathur , just out of curiosity, is there a way to manage lookupvalue, if it returns multiple dates with the selected month?

[Ashish_Mathur]

Share data in a format that can be pasted in an MS Excel file and show the expected result with an explanation.

[nalingupta94]

Hey @Ashish_Mathur, Please find the data attached:

Check year	Code	Date	Final Date
2019	1	1/01/2019	27/03/2019
2019	1	1/02/2019	27/03/2019
2019	1	3/03/2019	27/03/2019
2019	1	27/03/2019	27/03/2019
2019	1	27/05/2019	27/03/2019
2019	1	3/06/2019	27/03/2019
2019	1	25/06/2019	27/03/2019
2019	2	1/07/2019	28/08/2019
2019	2	25/07/2019	28/08/2019
2019	2	3/08/2019	28/08/2019
2019	2	28/08/2019	28/08/2019
2019	2	9/09/2019	28/08/2019
2019	2	11/09/2019	28/08/2019
2019	2	12/12/2019	28/08/2019
2020	1	1/01/2020	26/03/2020
2020	1	1/02/2020	26/03/2020
2020	1	3/03/2020	26/03/2020
2020	1	26/03/2020	26/03/2020
2020	1	27/05/2020	26/03/2020
2020	1	3/06/2020	26/03/2020
2020	1	25/06/2020	26/03/2020
2020	2	1/07/2020	25/08/2020
2020	2	25/07/2020	25/08/2020
2020	2	3/08/2020	25/08/2020
2020	2	25/08/2020	25/08/2020
2020	2	9/09/2020	25/08/2020
2020	2	11/09/2020	25/08/2020
2020	2	12/12/2020	25/08/2020
2021	1	1/01/2021	20/03/2021
2021	1	1/02/2021	20/03/2021
2021	1	3/03/2021	20/03/2021
2021	1	20/03/2021	20/03/2021
2021	1	27/05/2021	20/03/2021
2021	1	3/06/2021	20/03/2021
2021	1	25/06/2021	20/03/2021
2021	2	1/07/2021	29/08/2021
2021	2	25/07/2021	29/08/2021
2021	2	3/08/2021	29/08/2021
2021	2	29/08/2021	29/08/2021
2021	2	9/09/2021	29/08/2021
2021	2	11/09/2021	29/08/2021
2021	2	12/12/2021	29/08/2021

Explanation: If the Code is 1, select the MAX date March, where 'check year' = final date[year].
If the code is 02 select the MAX date in August where 'check year' = final date[year].

Please let me know if youd like any additional information.

[Ashish_Mathur]

I do not understand.  Show the expected result very clearly.

[nalingupta94]

Thanks so much @Ashish_Mathur.

Worked like a charm.

[amitchandak]

@nalingupta94 , A new column in dax

= if([code] =2 , date(Year([Date]), 8, day([Date]) ), [Date] )

[nalingupta94]

Hi @amitchandak,

this is pretty close, but I would like the day to be same as the day with month = 08.

e.g. if the dates are 10/09/2022(date A)  & 20/08/2022 (date B).

Date A should be replaced by date B.