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
v-sshirivolu's avatar
v-sshirivolu
Community Support
3 months ago

Hi SEwing ,
I would take a moment to thank ryan_mayu , for actively participating in the community forum and for the solutions you’ve been sharing in the community forum. Your contributions make a real difference.

 

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions