Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

Matrix condition based on week

Hello

I am still new to this conditional formatting in matrix

try to search on other case, still not working

 

I need help on Conditional formatting in matrix based on week 

I want this matrix visual but in week format

 

 

 

Thank you

 

Need Help
Show and Tell
Tips and Tricks
  • speedramps's avatar
    speedramps
    2 years ago

    Click here to download the solution

    Download PBIX 

     

    How it works ..

     

    Create a calendar table from start to end date

    Calendar =
    CALENDAR(DATE(2020,01,01),DATE(2023,12,31))
     
    Create a calulated column
    Week =
    YEAR('Calendar'[Date])
    & "-" &
    FORMAT(WEEKNUM('Calendar'[Date],2),"00")
     
     
    Buld a relationship to tehn calendar table

     

     

     

    Build your report

     

     

    Thanks for the clear description of the problem with example data. I wish everyone did that!

    Remember we are unpaid volunteers, and you have got free expert help which took a lot of effort,
    This solution works and does exactly what you asked.
    So please quickly click the [accept as solution] and the thumbs up button to leave kudos. 

    One question per ticket please. If you need to change or extend your request then please raise a new ticket.

    You will get a quicker response and each volunteer solver will get the kudos they deserve. Thank you ! 

    If you quote speedramps in your next tickets then I will then receive an automatic notification, and will be delighted to help you again.

    Please now click the [accept as solution] and the thumbs up button.  Thank you.

     

  • speedramps's avatar
    speedramps
    2 years ago

    Click here to download the solution, then please click accept solution.
    Click to download PBIX 

     

    How it works ...

     

    Create a measure 

    RAG = 
VAR mymindate = MIN('Calendar'[Date])
VAR previousweek = 
CALCULATE(
    MAX('Calendar'[Week]),
    'Calendar'[Date] < mymindate)
VAR previousvalue =
CALCULATE(
    SUM(Sales[Amount]),
    'Calendar'[Week] = previousweek)
VAR thisvalue =
SUM(Sales[Amount])

RETURN
SWITCH( 
    TRUE(),
thisvalue < previousvalue,"red",
thisvalue > previousvalue,"green"
)

     

    Then add field based colour formating 

     

     

5 Replies

  • speedramps's avatar
    speedramps
    Super User
    2 years ago

    Click here to download the solution

    Download PBIX 

     

    How it works ..

     

    Create a calendar table from start to end date

    Calendar =
    CALENDAR(DATE(2020,01,01),DATE(2023,12,31))
     
    Create a calulated column
    Week =
    YEAR('Calendar'[Date])
    & "-" &
    FORMAT(WEEKNUM('Calendar'[Date],2),"00")
     
     
    Buld a relationship to tehn calendar table

     

     

     

    Build your report

     

     

    Thanks for the clear description of the problem with example data. I wish everyone did that!

    Remember we are unpaid volunteers, and you have got free expert help which took a lot of effort,
    This solution works and does exactly what you asked.
    So please quickly click the [accept as solution] and the thumbs up button to leave kudos. 

    One question per ticket please. If you need to change or extend your request then please raise a new ticket.

    You will get a quicker response and each volunteer solver will get the kudos they deserve. Thank you ! 

    If you quote speedramps in your next tickets then I will then receive an automatic notification, and will be delighted to help you again.

    Please now click the [accept as solution] and the thumbs up button.  Thank you.

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      Hello speedramps , thank you for your answer. I'm sorry if my question is not clear.

      But what I mean is how to make the conditional formatting based on the week in the data.

       

      For example :

      if the second week = third week -> the background would be yellow

      if the 4th week > third week -> the background would be blue

       

      I manage to do that but with month 

      I made measure with date table :

      MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
      last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))

       

      Then have measures like

      diff = [MTD Sales]-[last MTD Sales]
      diff % = divide([MTD Sales]-[last MTD Sales],[last MTD Sales])

       

      Create a new measure

      color = Switch(true(),

      [Diff] <0, "pink",

      [Diff] <0, "Yellow",

      "blue"

      )

      Is there any work around to do similar with that but with week data ?

       

      • speedramps's avatar
        speedramps
        Super User
        2 years ago

        Please keep descriptions and clear and concise and just ask one question per ticket.

        Dont post your dax that does not work and expect us to fathom what you need.

        I have answered your weekly question.

         

        Having lots  > and - in your seconf question is very confussing.

        Why not simply say you want red if any week is less than previous week and green if its greater.

        Keep questions simple.

         

  • speedramps's avatar
    speedramps
    Super User
    2 years ago

    Click here to download the solution, then please click accept solution.
    Click to download PBIX 

     

    How it works ...

     

    Create a measure 

    RAG = 
VAR mymindate = MIN('Calendar'[Date])
VAR previousweek = 
CALCULATE(
    MAX('Calendar'[Week]),
    'Calendar'[Date] < mymindate)
VAR previousvalue =
CALCULATE(
    SUM(Sales[Amount]),
    'Calendar'[Week] = previousweek)
VAR thisvalue =
SUM(Sales[Amount])

RETURN
SWITCH( 
    TRUE(),
thisvalue < previousvalue,"red",
thisvalue > previousvalue,"green"
)

     

    Then add field based colour formating 

     

     

  • speedramps's avatar
    speedramps
    Super User
    2 years ago

    Thanks for thumbs up. Please also click accept as solution. It does exacty what you asked for.

    If you need something else then please raise a new ticket.