Measure: LOOKUP Date

[HenryJS]

Hi all,

The measure 'Timesheet?' below returns a "Yes" in the date field if the Week Ending lies within the PlacementStartDate and PlacementEndDate for that placement (Export Placements table).

Instead of "Yes" is it possible to have "Approved" if the CandidateRef is present for that week in the 'Timesheets Export' table? And just "Yes" if the date lies within Placement Start Date - Placement End Date. 

The measure for "Approved" will have to look up to the 'Timesheet Export'[Period Ending] date to see if a timesheet was approved in that week.

.pbix file:

https://www.dropbox.com/sh/q8czbh9haifer3a/AADaaXjf5VkrIztJuGAVfkFua?dl=0

Measure:

Timesheet? = IF(SELECTEDVALUE('Export Placements'[PlacementStartDate])<= MAX('Calendar'[Date]) && SELECTEDVALUE('Export Placements'[PlacementEndDate]) >= MAX('Calendar'[Date]), "Yes", BLANK())

[MFelix · Accepted solution]

Hi @HenryJS ,

Try the following measure:

Timesheet? =
VAR temp_table =
    FILTER (
        SUMMARIZE (
            'Timesheets export',
            'Timesheets export'[Period Ending],
            'Timesheets export'[Candidate Ref]
        ),
        'Timesheets export'[Candidate Ref]
            = SELECTEDVALUE ( 'Export Placements'[CandidateRef] )
    )
VAR Active =
    IF (
        MAXX ( temp_table; 'Timesheets export'[Period Ending] )
            <= MAX ( 'Calendar'[Date] )
            && MAXX ( temp_table; 'Timesheets export'[Period Ending] )
                >= MAX ( 'Calendar'[Date] ),
        "Active",
        ""
    )
VAR Yes_value =
    IF (
        SELECTEDVALUE ( 'Export Placements'[PlacementStartDate] )
            <= MAX ( 'Calendar'[Date] )
            && SELECTEDVALUE ( 'Export Placements'[PlacementEndDate] )
                >= MAX ( 'Calendar'[Date] ),
        "Yes",
        ""
    )
RETURN
    SWITCH ( TRUE (),Yes_value = "Yes" && Active = "Active", Active, Yes_value )

Check PBIX file attach.

[MFelix · Accepted solution]

Hi @HenryJS ,

Try the following:

Timesheet? =
VAR temp_table =
    FILTER (
        SUMMARIZE (
            'Timesheets export',
            'Timesheets export'[Period Ending],
            'Timesheets export'[Candidate Ref]
        ),
        'Timesheets export'[Candidate Ref]
            = SELECTEDVALUE ( 'Export Placements'[CandidateRef] )
            && 'Timesheets export'[Period Ending] = SELECTEDVALUE ( 'Calendar'[Week Ending] )
    )
VAR Active =
    IF (
        MAXX ( temp_table; 'Timesheets export'[Period Ending] )
            <= MAX ( 'Calendar'[Date] )
            && MAXX ( temp_table; 'Timesheets export'[Period Ending] )
                >= MAX ( 'Calendar'[Date] ),
        "Active",
        ""
    )
VAR Yes_value =
    IF (
        SELECTEDVALUE ( 'Export Placements'[PlacementStartDate] )
            <= MAX ( 'Calendar'[Date] )
            && SELECTEDVALUE ( 'Export Placements'[PlacementEndDate] )
                >= MAX ( 'Calendar'[Date] ),
        "Yes",
        ""
    )
RETURN
    SWITCH ( TRUE (), Yes_value = "Yes" && Active = "Active", Active, Yes_value )

These measure is based on the previous so if you may need to change the "" by blanks.

[MFelix · Accepted solution]

Hi @HenryJS ,

Try the following measure:

Timesheet? =
VAR temp_table =
    FILTER (
        SUMMARIZE (
            'Timesheets export',
            'Timesheets export'[Period Ending],
            'Timesheets export'[Candidate Ref]
        ),
        'Timesheets export'[Candidate Ref]
            = SELECTEDVALUE ( 'Export Placements'[CandidateRef] )
    )
VAR Active =
    IF (
        MAXX ( temp_table; 'Timesheets export'[Period Ending] )
            <= MAX ( 'Calendar'[Date] )
            && MAXX ( temp_table; 'Timesheets export'[Period Ending] )
                >= MAX ( 'Calendar'[Date] ),
        "Active",
        ""
    )
VAR Yes_value =
    IF (
        SELECTEDVALUE ( 'Export Placements'[PlacementStartDate] )
            <= MAX ( 'Calendar'[Date] )
            && SELECTEDVALUE ( 'Export Placements'[PlacementEndDate] )
                >= MAX ( 'Calendar'[Date] ),
        "Yes",
        ""
    )
RETURN
    SWITCH ( TRUE (),Yes_value = "Yes" && Active = "Active", Active, Yes_value )

Check PBIX file attach.

[HenryJS]

@MFelix Thanks so much for getting back to me. That looks like the resolution required.

Two things I was wondering if were possible:

• Empty rows to be BLANK i.e. not visible if the row has no Yes or Active in
• Totals (Counts) in the subheaders for each Week Ending

[MFelix]

Hi @HenryJS ,

Just replace the "" by Blank on both formulas:

Try the following measure for the total sum make a:

SUMX(Calendar[Date]; [Timesheet?])

[HenryJS]

@MFelix there's an issue with the measure you provided. If a candidate ref has more than one timesheet for different weeks in the 'Timesheets export' it will only pick up one from that table as 'Active'.

It needs to say Active for every week the candidate has a timesheet for.

An example is Anthony - 100252

He has a timesheet for WE 12/04 and 19/04

However table only says he has one for 19/04

Can you help?

Hope that makes sense

[MFelix · Accepted solution]

Hi @HenryJS ,

Try the following:

Timesheet? =
VAR temp_table =
    FILTER (
        SUMMARIZE (
            'Timesheets export',
            'Timesheets export'[Period Ending],
            'Timesheets export'[Candidate Ref]
        ),
        'Timesheets export'[Candidate Ref]
            = SELECTEDVALUE ( 'Export Placements'[CandidateRef] )
            && 'Timesheets export'[Period Ending] = SELECTEDVALUE ( 'Calendar'[Week Ending] )
    )
VAR Active =
    IF (
        MAXX ( temp_table; 'Timesheets export'[Period Ending] )
            <= MAX ( 'Calendar'[Date] )
            && MAXX ( temp_table; 'Timesheets export'[Period Ending] )
                >= MAX ( 'Calendar'[Date] ),
        "Active",
        ""
    )
VAR Yes_value =
    IF (
        SELECTEDVALUE ( 'Export Placements'[PlacementStartDate] )
            <= MAX ( 'Calendar'[Date] )
            && SELECTEDVALUE ( 'Export Placements'[PlacementEndDate] )
                >= MAX ( 'Calendar'[Date] ),
        "Yes",
        ""
    )
RETURN
    SWITCH ( TRUE (), Yes_value = "Yes" && Active = "Active", Active, Yes_value )

These measure is based on the previous so if you may need to change the "" by blanks.

[HenryJS]

@MFelix **bleep**! It says the syntax  for Timesheets export is incorrect?

Thanks for your help with this I really appreciate it.

Obrigado

[MFelix · Accepted solution]

My bad,

Due to regional settings my DAX measure have a ; instead of a , and I replaced incorrectly on the measure I posted check measure below rectified:

Timesheet? =
VAR temp_table =
    FILTER (
        SUMMARIZE (
            'Timesheets export',
            'Timesheets export'[Period Ending],
            'Timesheets export'[Candidate Ref]
        ),
        'Timesheets export'[Candidate Ref]
            = SELECTEDVALUE ( 'Export Placements'[CandidateRef] )
            && 'Timesheets export'[Period Ending] = SELECTEDVALUE ( 'Calendar'[Week Ending] )
    )
VAR Active =
    IF (
        MAXX ( temp_table; 'Timesheets export'[Period Ending] )
            <= MAX ( 'Calendar'[Date] )
            && MAXX ( temp_table, 'Timesheets export'[Period Ending] )
                >= MAX ( 'Calendar'[Date] ),
        "Active",
        ""
    )
VAR Yes_value =
    IF (
        SELECTEDVALUE ( 'Export Placements'[PlacementStartDate] )
            <= MAX ( 'Calendar'[Date] )
            && SELECTEDVALUE ( 'Export Placements'[PlacementEndDate] )
                >= MAX ( 'Calendar'[Date] ),
        "Yes",
        ""
    )
RETURN
    SWITCH ( TRUE (), Yes_value = "Yes" && Active = "Active", Active, Yes_value )

If you see any othe ; on the measure replace by comma.