Forum Discussion

Kelz's avatar
Kelz
Advocate I
4 years ago

Help: Traffic Light Staff Capacity formula

Hi,   I am trying to re-create a formula from one Power Bi report (built with Excel spreadsheets) into a new Power Bi (bulit with API's into the data) to show conditonal formatting in colour on a c...
v-robertq-msft's avatar
v-robertq-msft
Community Support
4 years ago

Hi, 

According to your description, I think you can make some changes on the measure [Free time] to get the requirement you wanted:

Free Time =

var _date=EOMONTH(TODAY(),-1)

var _weeks=divide(DATEDIFF(DATE(YEAR(_date),MONTH(_date),1),EOMONTH(_date,0),day),7)

var _sum=CALCULATE(SUM('Timesheet data'[hours]),FILTER(ALL('Timesheet data'),[Employee]=MAX('Timesheet data'[Employee])))

return

MAX('staff table'[Capacity])*_weeks-_sum

You can define the first variable _date according to your needs, in this case, is the last date in October

Then you can do the same thing as my above replies to achieve your needs:

Color =

SWITCH(

    TRUE(),

    [Free Time]<-10,"Red",

    [Free Time]>=-9&&[Free Time]<1,"Yellow",

    [Free Time]>1&&[Free Time]<=1000,"Green")

 

Thank you very much!

 

Best Regards,

Community Support Team _Robert Qin

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Kelz's avatar
Kelz
Advocate I
4 years ago

Hi Robert, 

 

sorry it didnt work. I dont want to show the "Free Time". I want to show the amount of hours that the staff member has done, and I want the color to be based on whether they have done their hours or not. I

also have a splicer on the dates, so I want it to change based on whatever month is choosen

 

 

 

Cheers

 

Kelz

 

 

  • KNP's avatar
    KNP
    Super User
    4 years ago

    Hi Kelz - Did you mark this as solved? I see it is marked as solved but your latest question came in after the "solution".

     

    • Kelz's avatar
      Kelz
      Advocate I
      4 years ago

      Hi, no I didn't, I think the system may have? or somebody might have if they can do that? and Im unsure how to revert it.

       

      The instructions Robert had were great and work well if I wanted to show the free time, but I wanted to show the hours the staff had worked for the month with the colour for each staff member based on whether they were under, over or at their contracted hours for month/s selected (I have a slicer on months and muiltple can be selected). 

       

      the original formula worked really well for the model I created using excel reports, but I've had to recreate the report using an API, so the names / tables are slightly different but still has the same information. 

       

      Thanks

      Kelly

      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago

        Kelz , KNP , I have unmarked the "Accepted Solution"  as it looks as Kelz  has not found the correct answer to their question.  If I am wrong, please advise.

         

        Thank you for being part of the community!

         

        cc: v-robertq-msft, Anonymous, chass