Forum Discussion

Pklu's avatar
Pklu
Frequent Visitor
2 years ago
Solved

Change the quarter months

I would ike to know how to change the quaters months to differnt months. I have a table call start date (renamed to Event Dates) with the following quaters:                         ...
Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi Pklu,

    You can add a new column to raw table to get the new quarter value. Then you can use date field year level, new quarter, date field month level to create a hierarchy slicer to achieve your requirement.

    Fiscal Quarter =
VAR offset = 3
VAR newQuarter =
    QUARTER (
        DATE ( YEAR ( Table1[StartDate] ), MONTH ( Table1[StartDate] ) + offset, DAY ( Table1[StartDate] ) )
    )
RETURN
    "Q" & newQuarter


    Regards,

    Xiaoxin Sheng

Anonymous's avatar
Anonymous
Not applicable
2 years ago

HI Pklu,

Table columns cannot directly be invoked in measure expression, I'd like to suggest you use aggregation functions to get the current value at first and stored in the variable.  Then you can use this variable value to compare with conditions.

Quarter to Month =
VAR currQuarter =
    SELECTEDVALUE ( 'Event Dates'[Quarter] )
RETURN
    SWITCH (
        currQuarter,
        "Q1", "October, November, December",
        "Q2", "January, February, March",
        "Q3", "April, May, June",
        "Q4", "July, August, September",
        BLANK ()
    )

Regards.

Xiaoxin Sheng

Pklu's avatar
Pklu
Frequent Visitor
2 years ago

Hello Sheng, Anonymous 

 

Tthank you so much for your code. The code worked but I had to change it to the following to get it to work properly.

 

Quarter to Month =
VAR currQuarter = SELECTEDVALUE ( 'Events Information'[Start Date].[Quarter] )
RETURN SWITCH
( currQuarter, "Q1", "October, November, December", "Q2", "January, February, March", "Q3", "April, May, June", "Q4", "July, August, September", BLANK () )
 
The field is called Start Date.
 
The Start Date field has the following data:
01/23/2023
04/25/2023
05/12/2023
06/13/2023
07/19/2023
08/15/2023
09/27/2023
11/02/2023
11/29/2023
12/17/2023
12/27/2023
 
The question is how do I add the new measure to the slicer? I tried to add it to the slicer but it would not show up in the field area. I tried dragging it also but it would not add it. Or how do I reference it so it shows up in the slicer so they can pick the different Qtrs.
 
Thanks.
  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi Pklu,

    You can add a new column to raw table to get the new quarter value. Then you can use date field year level, new quarter, date field month level to create a hierarchy slicer to achieve your requirement.

    Fiscal Quarter =
VAR offset = 3
VAR newQuarter =
    QUARTER (
        DATE ( YEAR ( Table1[StartDate] ), MONTH ( Table1[StartDate] ) + offset, DAY ( Table1[StartDate] ) )
    )
RETURN
    "Q" & newQuarter


    Regards,

    Xiaoxin Sheng