Forum Discussion

XenDance's avatar
XenDance
Regular Visitor
2 years ago
Solved

Calculating desk usage figures

Good afternoon everyone,   I'm trying to create some visualisations in Power BI for data I've been provided from a desk booking application. The application generates the following example data:  ...
Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi XenDance ,

     

    Okay, I've modified the conditions.

    Maximum Weekdays = 
VAR __today = TODAY()
VAR __cur_active_from_date = SELECTEDVALUE('Desks'[Active From])
VAR __active_from_date = IF(ISBLANK(__cur_active_from_date), MAX('Bookings'[Date]) , __cur_active_from_date)
VAR __result = IF(ISBLANK(__active_from_date) || __today<__cur_active_from_date, 0, NETWORKDAYS(__active_from_date,__today,1))
RETURN
__result
    Weekdays Booked = CALCULATE(COUNTROWS('Bookings'),FILTER('Bookings', NOT WEEKDAY('Bookings'[Date],2) IN {6,7} && 'Bookings'[Date]<=TODAY())) + 0

    Best Regards,
    Gao
    Community Support Team

     

    If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
    If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

    How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Hi XenDance ,

 

Please create 2 new measures:

Maximum Weekdays = 
VAR __today = TODAY()
VAR __cur_active_from_date = SELECTEDVALUE('Desks'[Active From])
VAR __active_from_date = IF(ISBLANK(__cur_active_from_date), MAX('Bookings'[Date]) , __cur_active_from_date)
VAR __result = IF(ISBLANK(__active_from_date), 0, NETWORKDAYS(__active_from_date,__today,1))
RETURN
__result
Weekdays Booked = CALCULATE(COUNTROWS('Bookings'),FILTER('Bookings', NOT WEEKDAY('Bookings'[Date],2) IN {6,7})) + 0

Best Regards,
Gao
Community Support Team

 

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

  • XenDance's avatar
    XenDance
    Regular Visitor
    2 years ago

    Hi Anonymous 

     

    Many thanks for taking the time to provide this solution.  However, I am encountered two issues:

     

    • I failed to mention in my initial post that there are instances where the 'Active From' date in the desks table is a date in the future.  This throws my calculation off since I get a negative number for the purposes of calculating the percentage.  Is it possible to amend this measure so it treats 'Active From' dates in the future the same as a blank date (i.e. maximum weekdays should equal 0)?
    • The weekdays booked figure includes bookings made in the future (i.e. greater than todays date).  I would like to disregard these for the purposes of the calculation.  Apologies if I did not make this clear in my initial message!

     

    Other than these issues, this is exactly what I was looking for - thank you for spending the time to look at this.  I'm hoping it is a relatively minor change (I'm a bit lost when it comes to looking at the DAX you provided) so I appreciate any further assistance you can provide!

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      Hi XenDance ,

       

      Okay, I've modified the conditions.

      Maximum Weekdays = 
VAR __today = TODAY()
VAR __cur_active_from_date = SELECTEDVALUE('Desks'[Active From])
VAR __active_from_date = IF(ISBLANK(__cur_active_from_date), MAX('Bookings'[Date]) , __cur_active_from_date)
VAR __result = IF(ISBLANK(__active_from_date) || __today<__cur_active_from_date, 0, NETWORKDAYS(__active_from_date,__today,1))
RETURN
__result
      Weekdays Booked = CALCULATE(COUNTROWS('Bookings'),FILTER('Bookings', NOT WEEKDAY('Bookings'[Date],2) IN {6,7} && 'Bookings'[Date]<=TODAY())) + 0

      Best Regards,
      Gao
      Community Support Team

       

      If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
      If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

      How to get your questions answered quickly --  How to provide sample data in the Power BI Forum