Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Create calculated table and Return earliest date from Sum Original Table

I need help completing the formular below is possible .

LastDate = SUMMARIZE('Table',[Value],"Status", IF(CALCULATE(COUNT('Table'[Value]),'Table'[Type]="cs-accelerator-CS Accelerator Assessment",'Table'[State]="Failed")>=1,"Passed","No"))
 I would like to return the earliest date from "Table"[Date] if the logical test is true and "No" if its false .
 
Please see screenshot attached 
Need Help

4 Replies

  • mahoneypat's avatar
    mahoneypat
    Microsoft Employee
    6 years ago

    Hard to be sure w/o seeing your data and model, but try this measure:

     

    LastDate =
    VAR summarytable =
    SUMMARIZE (
    Table,
    Table[Value],
    Table[Date],
    "FailedCount", CALCULATE (
    COUNT ( 'Table'[Value] ),
    'Table'[Type] = "cs-accelerator-CS Accelerator Assessment",
    'Table'[State] = "Failed"
    ) + 0
    )
    VAR filtered =
    FILTER ( summarytable, "FailedCount" >= 0 )
    RETURN
    MINX ( filtered, Table[Date] )

     

    If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

    Regards,

    Pat

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      The syntax for 'Table' is incorrect. (DAX(VAR summarytable =SUMMARIZE (Table,Table[Value],Table[Date],"FailedCount", CALCULATE (COUNT ( 'Table'[Value] ),'Table'[Type] = "cs-accelerator-CS Accelerator Assessment",'Table'[State] = "Failed") + 0)VAR filtered =FILTER ( summarytable, "FailedCount" >= 0 )RETURNMINX ( filtered, Table[Date] ) )).

       

      mahoneypat  says the syntax is incorrect 

  • v-chuncz-msft's avatar
    v-chuncz-msft
    Community Support
    6 years ago

    Anonymous 

     

    Just change it as follows.

            CONVERT ( MIN ( 'Table'[Date] ), STRING ),
        "No"

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      v-chuncz-msft  this is on step closer to where I would like to be but it is returning min date instead of Earlierst . Please see screenshot attached.

       

      It should return 14/01/2020

       

      v-chuncz-msft