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
Shai_Karmani's avatar
Shai_Karmani
Super User
3 months ago

You can do this with a calculated column that returns each site's true completion date (the date when its highest-numbered job was completed), then count distinct sites in your visual.

Add this calculated column to your fact table:

Site Complete Date = 
VAR _site = 'Table'[Site]
VAR _maxJob = CALCULATE ( MAX ( 'Table'[Job Number] ), 'Table'[Site] = _site )
RETURN
    CALCULATE (
        MAX ( 'Table'[Completion Date] ),
        'Table'[Site] = _site,
        'Table'[Job Number] = _maxJob
    )

For Delta this returns BLANK because the last job (Job 3) has no completion date, so Delta drops out automatically. In your chart, put Month from Site Complete Date on the axis and use DISTINCTCOUNT('Table'[Site]) as the value, then add a visual-level filter where Site Complete Date is not blank. That gives you Feb=1, Mar=1, Apr=1.

If this helped, please give it a thumbs up and mark it as the accepted solution.

Best,
Shai Karmani

SEwing's avatar
SEwing
Frequent Visitor
3 months ago

That ALMOST works! Is there a way to do it by the latest completed date maybe? I simplified the JOB numbers. Those are actually work order numbers and they're all over the place (B34822, W387664, GL8439383, etc.)