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:

SiteJob NumberJob Completion DateMeasure of Completed Sites Dates Count of Sites/Month 
Alphab342/1/20262/1/2026 FebMarApr 
BravoX342/10/2026  120 
BravoJ152/15/2026      
BravoR213/2/20263/2/2026     
CharlieL143/15/2026      
CharlieX273/15/2026      
CharlieD113/15/20263/15/2026     
DeltaF154/10/2026      
Delta X34       
   Measure should not count duplicate dates and not return a date if any Job not completed for a Site

 

So I want it to show Feb 1, March - 2, April - 0.  (Alpha was complete in Feb, Bravo and Charlie were complete in Mar, and Delta is not yet complete.

 

Edited for clarification....

Any help would be appreciated!

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

8 Replies

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

  • ryan_mayu's avatar
    ryan_mayu
    Super User
    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.)

  • 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