Forum Discussion

MiloDi's avatar
MiloDi
Frequent Visitor
2 years ago
Solved

Return Max date based on multiple condition

Hello dear community

 

I can't get out of a worry.
I can express it, but I can't put it into words.

Here you have an exel chart.
On the left you have a small sample of data and on the right the expected result.

I have several of the same file numbers for different tasks.
The start date must always be the date corresponding to name, which contains AB.
The end date is a little more complex. I take the oldest date of the lines only if each line has the status TRUE if a line doesn't have the status True then it's empty.

Any ideas?

Thank you very much in advance

Files  

Need Help
  • Daniel29195's avatar
    Daniel29195
    2 years ago

    hello MiloDi 

    output 

     

     

    measure 1 --  start date 

    Start Date = 
var start_date = 
FILTER(
    Tableau1,
    Tableau1[Name] = "AB"
)
return 
MINX(start_date,Tableau1[Date])

     

     

    measure 2 --  end date

    End  Date = 
var count_all =  COUNTROWS(Tableau1)
var end_true = 
FILTER(
    Tableau1,
    Tableau1[Status] = TRUE()
)

 var count_true =  COUNTROWS(end_true)
var res = 
SWITCH(
    TRUE(),
    count_all <> count_true , blank(),
MAXX(end_true,[Date])
)
return  res

     

     

     

    let me know if this works for you .

     

     

     

    If my answer helped sort things out for you, i would appreciate a thumbs up 👍 and mark it as the solution ✅
    It makes a difference and might help someone else too. Thanks for spreading the good vibes! 🤠

     

  • Daniel29195's avatar
    Daniel29195
    2 years ago

    MiloDi 

     

    modify the code to this : 

    End  Date = 
var count_all =  calculate(COUNTROWS(Tableau1) , REMOVEFILTERS(Tableau1[Status]))
var end_true = 
FILTER(
    Tableau1,
    Tableau1[Status] = TRUE()
)

 var count_true =  COUNTROWS(end_true)
var res = 
SWITCH(
    TRUE(),
    count_all <> count_true , blank(),
MAXX(end_true,[Date])
)
return  res

     

     

     

    let me know if this helps ....

     

     

     

    If my answer helped sort things out for you, i would appreciate a thumbs up 👍 and mark it as the solution ✅
    It makes a difference and might help someone else too. Thanks for spreading the good vibes! 🤠

4 Replies

  • Daniel29195's avatar
    Daniel29195
    Community Champion
    2 years ago

    hello MiloDi 

    output 

     

     

    measure 1 --  start date 

    Start Date = 
var start_date = 
FILTER(
    Tableau1,
    Tableau1[Name] = "AB"
)
return 
MINX(start_date,Tableau1[Date])

     

     

    measure 2 --  end date

    End  Date = 
var count_all =  COUNTROWS(Tableau1)
var end_true = 
FILTER(
    Tableau1,
    Tableau1[Status] = TRUE()
)

 var count_true =  COUNTROWS(end_true)
var res = 
SWITCH(
    TRUE(),
    count_all <> count_true , blank(),
MAXX(end_true,[Date])
)
return  res

     

     

     

    let me know if this works for you .

     

     

     

    If my answer helped sort things out for you, i would appreciate a thumbs up 👍 and mark it as the solution ✅
    It makes a difference and might help someone else too. Thanks for spreading the good vibes! 🤠

     

    • MiloDi's avatar
      MiloDi
      Frequent Visitor
      2 years ago

      Good afternoon, Daniel29195, 

       

      First thank you for your time and your answer. 

      In my case its not working properly , I have date for Id who have one false statut. 

      I join you the PBI, perhaps I do soemthing wrong. 

      Can you guide me ? 

       

      thank you in advance

       

      https://we.tl/t-4iYlUSEeTh 

      • Daniel29195's avatar
        Daniel29195
        Community Champion
        2 years ago

        MiloDi 

         

        modify the code to this : 

        End  Date = 
var count_all =  calculate(COUNTROWS(Tableau1) , REMOVEFILTERS(Tableau1[Status]))
var end_true = 
FILTER(
    Tableau1,
    Tableau1[Status] = TRUE()
)

 var count_true =  COUNTROWS(end_true)
var res = 
SWITCH(
    TRUE(),
    count_all <> count_true , blank(),
MAXX(end_true,[Date])
)
return  res

         

         

         

        let me know if this helps ....

         

         

         

        If my answer helped sort things out for you, i would appreciate a thumbs up 👍 and mark it as the solution ✅
        It makes a difference and might help someone else too. Thanks for spreading the good vibes! 🤠