Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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.
Solved! Go to 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"))
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"))
Hi @Ashish_Mathur , just out of curiosity, is there a way to manage lookupvalue, if it returns multiple dates with the selected month?
Share data in a format that can be pasted in an MS Excel file and show the expected result with an explanation.
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.
I do not understand. Show the expected result very clearly.
@nalingupta94 , A new column in dax
= if([code] =2 , date(Year([Date]), 8, day([Date]) ), [Date] )
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.
User | Count |
---|---|
84 | |
80 | |
72 | |
71 | |
55 |
User | Count |
---|---|
108 | |
106 | |
93 | |
84 | |
66 |