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. ...
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

Adescrit's avatar
Adescrit
Impactful 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
    Community 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