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

DallasBaba's avatar
DallasBaba
Icon for Skilled Sharer rankSkilled Sharer
2 years ago

Pklu you can create a DAX measure that maps the quarters to the corresponding months. Here is an example of how you can do this:

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

This measure uses the SWITCH function to map each quarter to the corresponding months. If the quarter is not one of the specified values, the measure returns a blank value.

 

Let me know if this works for you. @ me in replies, or I'll lose your thread!!!  You can also include a pix file with the sample data you are working with for easy debugging. 
Note:
If this post is helpful, please mark it as the solution to help others find it easily. Also, if my answers contribute to a solution, show your appreciation by giving it a thumbs up! 
  • Pklu's avatar
    Pklu
    Frequent Visitor
    2 years ago

    Hello DallasBaba 

     

    This is the error I am getting. It says the table not found but it is there. here is the screen shot for you to see.

    The date name is start date.

     

     

    And also the error messaage.

    Thanks

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