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.

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