Forum Discussion

SEwing's avatar
SEwing
Frequent Visitor
3 months ago
Solved

How to use latest date from multiple rows for visualization

I'm trying to create visual for Site Job Completions by month. The data looks like this: Site Job Number Job Completion Date Measure of Completed Sites Dates   Count of Sites/Month   Alp...
Need Help
  • ryan_mayu's avatar
    ryan_mayu
    3 months ago

    SEwing 

    you can create a new table and use the new table to display the result

     

    SummaryTable =
    VAR BaseTable =
        ADDCOLUMNS (
            SUMMARIZE ( 'Jobs', 'Jobs'[Site] ),
            "Max Job Completion Date",
                VAR HasBlankDate =
                    CALCULATE (
                        COUNTROWS ( 'Jobs' ),
                        KEEPFILTERS ( 'Jobs'[Job Completion Date] = BLANK () )
                    )
                VAR MaxNonBlankDate =
                    CALCULATE (
                        MAX ( 'Jobs'[Job Completion Date] ),
                        'Jobs'[Job Completion Date] <> BLANK ()
                    )
                RETURN
                    IF (
                        HasBlankDate > 0,
                        BLANK (),          -- Max Job Completion Date (Delta = blank)
                        MaxNonBlankDate
                    )
        )
    RETURN
    ADDCOLUMNS (
        BaseTable,
        "Month",
            VAR MaxNonBlankDateForSite =
                CALCULATE (
                    MAX ( 'Jobs'[Job Completion Date] ),
                    'Jobs'[Job Completion Date] <> BLANK ()
                )
            RETURN
                IF (
                    ISBLANK ( MaxNonBlankDateForSite ),
                    BLANK (),
                    FORMAT ( MaxNonBlankDateForSite, "MMM" )   -- or "MMMM"
                )
    )
    pls see the attachment below
Charlotte_1234's avatar
Charlotte_1234
New Member
3 months ago

Hi
You should be able to use a calculated column.  I tested this with the small dataset you provided and it seemed to work ok

SiteCompletionDate = 
VAR ThisSite = 'Table'[Site]   -- current Site

VAR HasIncompleteJobs =
    CALCULATE (
        COUNTBLANK ( 'Table'[Completion Date] ),  -- count blank dates
        ALLEXCEPT ( 'Table', 'Table'[Site] )      -- keep only Site context
    ) > 0   -- TRUE if any blanks exist

VAR LastCompletionDate =
    CALCULATE (
        MAX ( 'Table'[Completion Date] ),         -- latest completion date
        ALLEXCEPT ( 'Table', 'Table'[Site] )
    )

RETURN
    IF ( HasIncompleteJobs, BLANK (), LastCompletionDate )
SEwing's avatar
SEwing
Frequent Visitor
3 months ago

Small problem...When Job 1 and Job 2 have the same completion date, it counts them both. It should only count one.