Forum Discussion

RichWyeth's avatar
RichWyeth
Frequent Visitor
9 years ago
Solved

Lookup Values and Measures

Hi,   I have a table that holds UK Bank Holiday Dates and I have used a Lookup value to pull the correct number of days into another table. I use this against a weekday calc to subtract the bank ho...
Need Help
  • v-yulgu-msft's avatar
    v-yulgu-msft
    9 years ago

    Hi RichWyeth,

     

    Finally, your expect result is to get the actual working days per month, right?

     

    If so, in the weekday table, please add these calculated columns:

    Holiday =
    LOOKUPVALUE ( 'Bank Holidays'[Dates], 'Bank Holidays'[Dates], Weekdays[Date] )

IF =
    IF ( Weekdays[Date] = Weekdays[Holiday], 0, 1 )

MonthName = MONTH(Weekdays[Date])
     
    Create a measure:
    Actual working days =
    CALCULATE ( SUM ( Weekdays[IF] ), ALLEXCEPT ( Weekdays, Weekdays[MonthName] ) )
     
    By the way, I didn't create a relationship between these two tables.
     
    If you still have any question, please feel free to ask.
     
    Best regards,
    Yuliana Gu



     

v-yulgu-msft's avatar
v-yulgu-msft
Microsoft Employee
9 years ago

Hi RichWyeth,

 

Finally, your expect result is to get the actual working days per month, right?

 

If so, in the weekday table, please add these calculated columns:

Holiday =
    LOOKUPVALUE ( 'Bank Holidays'[Dates], 'Bank Holidays'[Dates], Weekdays[Date] )

IF =
    IF ( Weekdays[Date] = Weekdays[Holiday], 0, 1 )

MonthName = MONTH(Weekdays[Date])
 
Create a measure:
Actual working days =
    CALCULATE ( SUM ( Weekdays[IF] ), ALLEXCEPT ( Weekdays, Weekdays[MonthName] ) )
 
By the way, I didn't create a relationship between these two tables.
 
If you still have any question, please feel free to ask.
 
Best regards,
Yuliana Gu



 

RichWyeth's avatar
RichWyeth
Frequent Visitor
9 years ago

Thanks v-yulgu-msft

 

Always learning something new, long way to go but getting there.