Forum Discussion

Sjmiller's avatar
Sjmiller
Regular Visitor
2 years ago
Solved

Convert period to consistent time frame

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

name# AllowedPeriodUsed
Jones3Week2
Smith5Month1
Weston1Quarter5
Marley8Month3
King4Week4
Johnson9Quarter8


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. 

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    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

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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