Forum Discussion

jcarter0713's avatar
jcarter0713
New Member
2 years ago

Changing Formula based on Date Selection

Hello,

 

I am looking to create a utiization chart based on room utilization. The room will have a sum of the amount of hours used and I want to divide this by the total possible hours in a month to get the utilization %. This part is working fine. However, when I drill up to a year, the math is off because the dividing number is not large enough (Needs to be multiplied by 12). Is there a way to make this work with a single formula that changes based on the Date view of Month or Year?

Need Help

1 Reply

  • amitchandak's avatar
    amitchandak
    Super User
    2 years ago

    jcarter0713 , You can get month of the date in context using

     

    Month(Max(Date[Date]))

     

    other ways

    or

    Utilization % =
    VAR CurrentLevel = ISINSCOPE('Date'[Year]) + ISINSCOPE('Date'[Month])
    VAR HoursUsed = SUM('RoomData'[HoursUsed])
    VAR TotalHours =
    SWITCH(
    CurrentLevel,
    2, EOMONTH(MAX('Date'[Date]), 0) - EOMONTH(MIN('Date'[Date]), -1) + 1, // Monthly level
    1, 365 * (YEAR(MAX('Date'[Date])) - YEAR(MIN('Date'[Date])) + 1), // Annual level
    BLANK()
    )
    RETURN
    IF(
    NOT ISBLANK(TotalHours),
    HoursUsed / TotalHours,
    BLANK()
    )