Summarize dates without blanks

[Anonymous]

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 has no blank dates against it?

MaxID = SUMMARIZE(table1,table1[Id],"LastEntryDate",max(table1[date]))

[Anonymous]

Hey @Anonymous 

There are many options.

For example.

For Example.

Cheers!
A

[vivran22]

Hi,

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

Rgds,
Vivek

[Anonymous]

Id	LastEntryDate
11	23/05/2019 0:00
12	23/05/2019 0:00
13	23/05/2019 0:00
24	24/05/2019 0:00
25	24/05/2019 0:00
26
49
50
51	31/05/2019 0:00
52	8/06/2019 0:00
55	11/06/2019 0:00

[novilpawar · Accepted solution]

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

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? 

[novilpawar]

Try this-

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

Regards,

Novil

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

[TomMartens]

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