Forum Discussion

user_dil's avatar
user_dil
Frequent Visitor
3 years ago
Solved

Hiding Values for the Current quarter

Hi,

I have a matrix table with quarterly growth values. In that, I want to hide the growth value for the current quarter. Once the quarter is completed, the growth value should appear dynamically.

Quarter Index goes as follows;

Apr-Jun: Q1

Jul - Sept: Q2

Oct-Dec: Q3

Jan - Mar: Q4

 

Thanks!

 

 

Need Help
  • v-yueyunzh-msft's avatar
    v-yueyunzh-msft
    3 years ago

    Hi , user_dil 

    According to your description, you want to get the quarter by custom.

    For your need , you can try to add a calculated column in your date table like this:

    Column = var _year = YEAR([Date])
var _month =  MONTH([Date])

return
SWITCH(TRUE() , 
_month>=8 && _month<=10 , _year*100 +1,
_month>=11 || _month=1 , _year*100 +2,
_month>=2 && _month<=4 , (_year-1)*100 +3,
_month>=5 && _month<=7 , (_year-1)*100 +4
)

    The result is as follows:

    The "202301" means "2023-Q1" ; "202302" means "2023-Q2".

     

    Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

     

    Best Regards,

    Aniya Zhang

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

3 Replies

  • Adescrit's avatar
    Adescrit
    Icon for Impactful Individual rankImpactful Individual
    3 years ago

    Hi user_dil 

    You will need to create a calendar dimension table if you don't have one already.

    In that calendar dimension table, create a Quarter Index column with this DAX:

    Quarter Index = DATEDIFF( TODAY(), [Date], QUARTER )
     
    Then on your matrix visual, add the quarter index column to the "Filters on this visual" section of the filter pane, and unselect Quarter Index "0", i.e. the current quarter.
    • user_dil's avatar
      user_dil
      Frequent Visitor
      3 years ago

      Hi Adescrit 

       

      This seems working for the given quarter ranges. In case of a different quarter range, how can I change the DAX?

      Eg: Aug-Oct: Q1, Nov-Jan: Q2,...

      • v-yueyunzh-msft's avatar
        v-yueyunzh-msft
        Icon for Community Support rankCommunity Support
        3 years ago

        Hi , user_dil 

        According to your description, you want to get the quarter by custom.

        For your need , you can try to add a calculated column in your date table like this:

        Column = var _year = YEAR([Date])
var _month =  MONTH([Date])

return
SWITCH(TRUE() , 
_month>=8 && _month<=10 , _year*100 +1,
_month>=11 || _month=1 , _year*100 +2,
_month>=2 && _month<=4 , (_year-1)*100 +3,
_month>=5 && _month<=7 , (_year-1)*100 +4
)

        The result is as follows:

        The "202301" means "2023-Q1" ; "202302" means "2023-Q2".

         

        Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

         

        Best Regards,

        Aniya Zhang

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