Forum Discussion

HenryJS's avatar
HenryJS
Post Prodigy
6 years ago
Solved

Measure: LOOKUP Date

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())
 
 
 
 
 
 
 
Data Protection
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • MFelix's avatar
    MFelix
    6 years ago

    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's avatar
    MFelix
    6 years ago

    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's avatar
    MFelix
    6 years ago

    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.

     

7 Replies

  • MFelix's avatar
    MFelix
    Super User
    6 years ago

    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's avatar
      HenryJS
      Post Prodigy
      6 years ago

      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's avatar
        MFelix
        Super User
        6 years ago

        Hi HenryJS ,

         

        Just replace the "" by Blank on both formulas:

         

        Try the following measure for the total sum make a:

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