Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Summarize dates without blanks

Hi all,   I need help with the following dax. I am creating a table that summerizes the latest dates by id. This works well however I have blank dates. How can I get the latest dates where the id h...
Need Help
  • novilpawar's avatar
    novilpawar
    7 years ago

    Anonymous hi

    I have written simple dax similar to your dax except i have applied filter which remove blank dates rows. 

    It have worked for me. 

     

    table2 = SUMMARIZE(FILTER(Table1,Table1[DATE] <> BLANK()),Table1[ID],"MAX DATE",MAX(Table1[DATE]))

     

    Regards,

    Novil

    If I answer you question, please mark my post as solution.

vivran22's avatar
vivran22
Community Champion
7 years ago
Hi,

Is it possible for you to share some sample table and the desired output?

Rgds,
Vivek
  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago
    IdLastEntryDate
    1123/05/2019 0:00
    1223/05/2019 0:00
    1323/05/2019 0:00
    2424/05/2019 0:00
    2524/05/2019 0:00
    26 
    49 
    50 
    5131/05/2019 0:00
    528/06/2019 0:00
    5511/06/2019 0:00
    • novilpawar's avatar
      novilpawar
      Advocate I
      7 years ago

      Anonymous hi

      I have written simple dax similar to your dax except i have applied filter which remove blank dates rows. 

      It have worked for me. 

       

      table2 = SUMMARIZE(FILTER(Table1,Table1[DATE] <> BLANK()),Table1[ID],"MAX DATE",MAX(Table1[DATE]))

       

      Regards,

      Novil

      If I answer you question, please mark my post as solution.

      • MAAbdullah_47's avatar
        MAAbdullah_47
        Helper V
        5 years ago

        Hi 

        If the other column other than the summerized need also to be Non-Blank for example in our case (Table1[ID]) how can we do that? 

    • TomMartens's avatar
      TomMartens
      Super User
      7 years ago

      Hey,

       

      I use this kind of DAX statement:

      Table = 
FILTER(
    ADDCOLUMNS( 
        SUMMARIZE(
            VALUES(Table1[Id])
            , Table1[Id]
        )
        , "the latest date" , CALCULATE(LASTNONBLANK('Table1'[LastEntryDate] , MAX('Table1'[LastEntryDate]) ))
    )
    , [the latest date] <> BLANK()
)

      This creates a table like this:

      As you can see from the screenshot from above, there really are no blank rows :-)

       

      You have to be aware that you have to use TREATAS(...) if you want to use the summarized table as a table filter, as the calculation of the last date breaks the lineage to the column [LastEntryDate] like so:

       

      a measure = 
var thesummarizedtable = ...
return
CALCULATE(
...
, TREATAS(<thesummarizedtable> , '<the table name>'[Id] , '<the table name>'[LastEntryDate])
)

      Hopefully this is what you are looking for.

       

      Regards,

      Tom