Convert period to consistent time frame

[Sjmiller]

I have some data that I am not sure how to convert. It looks like this:

name	# Allowed	Period	Used
Jones	3	Week	2
Smith	5	Month	1
Weston	1	Quarter	5
Marley	8	Month	3
King	4	Week	4
Johnson	9	Quarter	8

I need to be able conditionally format the Used column when they are under their allowed #. So for a given date such as 4/1/23-4/28/2023 if Jones used 2 total, but was allowed 3 a week he would be drastically under his allowed and would go red or whatever.  I need to figure out how to I think first put all these in the same type of time frame.  But the date slicer can be anything, maybe I should lock this somehow? but still unsure the best route to convert all to the same time frame. 

[Anonymous · Accepted solution]

HI @Sjmiller,

I'd like to suggest you write a measure formula to compare with allowed and used amount based on current user and period group and return different color code to use in conditional formatting.

Apply conditional table formatting in Power BI - Power BI | Microsoft Learn

formula=
VAR allowed =
    CALCULATE (
        SUM ( Table1[# Allowed] ),
        ALLSELECTED ( Table1 ),
        VALUES ( Table1[name] ),
        VALUES ( Table1[Period] )
    )
VAR used =
    CALCULATE (
        SUM ( Table1[Used] ),
        ALLSELECTED ( Table1 ),
        VALUES ( Table1[name] ),
        VALUES ( Table1[Period] )
    )
RETURN
    IF ( allowed > used, "Green", IF ( allowed = used, "Yellow", "Red" ) )

Regards,
Xiaoxin Sheng