Forum Discussion

nalingupta94's avatar
nalingupta94
Frequent Visitor
2 years ago
Solved

Finding required date.

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 ...
Need Help
Show and Tell
  • Ashish_Mathur's avatar
    Ashish_Mathur
    2 years ago

    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"))

     

     

Ashish_Mathur's avatar
Ashish_Mathur
Super User
2 years ago

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's avatar
    nalingupta94
    Frequent Visitor
    2 years ago

    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's avatar
      Ashish_Mathur
      Super User
      2 years ago

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

      • nalingupta94's avatar
        nalingupta94
        Frequent Visitor
        2 years ago

        Hey Ashish_Mathur, Please find the data attached:

        Check year

        Code

        Date

        Final Date

        201911/01/201927/03/2019
        201911/02/201927/03/2019
        201913/03/201927/03/2019
        2019127/03/201927/03/2019
        2019127/05/201927/03/2019
        201913/06/201927/03/2019
        2019125/06/201927/03/2019
        201921/07/201928/08/2019
        2019225/07/201928/08/2019
        201923/08/201928/08/2019
        2019228/08/201928/08/2019
        201929/09/201928/08/2019
        2019211/09/201928/08/2019
        2019212/12/201928/08/2019
        202011/01/202026/03/2020
        202011/02/202026/03/2020
        202013/03/202026/03/2020
        2020126/03/202026/03/2020
        2020127/05/202026/03/2020
        202013/06/202026/03/2020
        2020125/06/202026/03/2020
        202021/07/202025/08/2020
        2020225/07/202025/08/2020
        202023/08/202025/08/2020
        2020225/08/202025/08/2020
        202029/09/202025/08/2020
        2020211/09/202025/08/2020
        2020212/12/202025/08/2020
        202111/01/202120/03/2021
        202111/02/202120/03/2021
        202113/03/202120/03/2021
        2021120/03/202120/03/2021
        2021127/05/202120/03/2021
        202113/06/202120/03/2021
        2021125/06/202120/03/2021
        202121/07/202129/08/2021
        2021225/07/202129/08/2021
        202123/08/202129/08/2021
        2021229/08/202129/08/2021
        202129/09/202129/08/2021
        2021211/09/202129/08/2021
        2021212/12/202129/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.