Quarter organization

[Anonymous]

I have a matrix in which I'm using quarters to define my columns. My matrix currently looks like this

Full Year           Q1            Q2             Q3            Q4

I want it to look like this though,

Q1             Q2              Q3              Q4         Full Year

I tried adding a conditional column and organizing Q1 as A, Q2 as B, and so on, and it did not seem to work. If anyone knows how to do this please let me know.

[v-kaiyue-msft · Accepted solution]

Hi @Anonymous ,

Thanks for the reply from @aduguid , please allow me to provide another insight: 

1. Create a calculation table and assign values ​​to the quarters in order.

Table 2 =
ADDCOLUMNS(DISTINCT('Table'[Quarter]),"num",SWITCH('Table'[Quarter],
"Q1",1,
"Q2",2,
"Q3",3,
"Q4",4,
"full year",5))

2. Sort the Quarter column by the num column.

3. Create a relationship between the two tables.

4. Put the dax of the calculation table in the column of the matrix.

If your Current Period does not refer to this, please clarify in a follow-up reply.

Best Regards,

Clara Gong

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

[aduguid]

You could just add a total column and change the label to full year.

[Anonymous]

I had this at first but the full year column as a total only has the ability to do average, mean, median, sum etc..., but I need a different calculation as on of the cells that forces me to not use column totals.

[aduguid]

The only other way I can think of is to use a DAX query to create the timeframes (Q1,Q2,Q3,Q4,Full Year) and assign an order to them. Then you can join it to your calendar table.

Here's an example of a timeframe table I use.

Calendar Timeframe = 
VAR _today_date =                   TODAY()  //'Properties'[Today Date]
VAR _yesterday_date =               _today_date - 1
VAR _week_start =                   _today_date - WEEKDAY ( _today_date, 2 )
VAR _week_end =                     _today_date - WEEKDAY ( _today_date, 2 ) + 6
VAR _month_start =                  DATE( YEAR(_today_date), MONTH(_today_date), 01 )
VAR _month_end =                    EOMONTH( _today_date, 0)
VAR _quarter_start =                DATE ( YEAR (_today_date), ROUNDUP ( DIVIDE ( MONTH (_today_date), 3 ), 0 ) * 3 - 2, 1 )
VAR _quarter_end =                  EOMONTH(EDATE(_quarter_start, 2), 0) 
VAR _fiscal_year =                  YEAR(EDATE( _today_date, 6))
VAR _fiscal_year_start =            DATE( _fiscal_year - 1, 07, 01)
VAR _fiscal_year_end =              DATE( _fiscal_year, 06, 30)
VAR _tomorrow_date =                IF(_today_date + 1 > _fiscal_year_end, _fiscal_year_end, _today_date + 1)
VAR _calendar_year =                YEAR(_today_date)
VAR _calendar_year_start =          DATE( _calendar_year , 01, 01)
VAR _calendar_year_end =            DATE( _calendar_year, 12, 31)
VAR _previous_month_start =         IF(MONTH(_today_date) = 1, DATE(YEAR(_today_date)-1, 12, 1), DATE(YEAR(_today_date), MONTH(_today_date)-1, 1))
VAR _previous_month_end =           DATE(YEAR(_previous_month_start), MONTH(_previous_month_start), DAY(EOMONTH(_previous_month_start, 0)))
VAR _previous_quarter_start =       EDATE(_quarter_start, -3) 
VAR _previous_quarter_end =         EOMONTH(EDATE(_quarter_start, -1), 0) 
VAR _previous_fiscal_year_start =   DATE( _fiscal_year - 2, 07, 01)
VAR _previous_fiscal_year_end =     DATE( _fiscal_year - 1, 06, 30)
VAR _previous_calendar_year_start = DATE( _calendar_year - 1, 01, 01)
VAR _previous_calendar_year_end =   DATE( _calendar_year - 1, 12, 31)
VAR _today_date_py =                DATE( YEAR(_today_date) - 1, MONTH(_today_date), DAY(_today_date) )
VAR _week_start_py =                DATE( YEAR(_today_date_py), 1 , 1) + (WEEKNUM(_today_date_py) - 1 ) * 7
VAR _month_start_py =               DATE( YEAR(_today_date_py), MONTH(_today_date), 01 )
VAR _quarter_start_py =             DATE( YEAR(_quarter_start) - 1, MONTH(_quarter_start), 01 )
VAR _fiscal_year_start_py =         DATE( YEAR(_fiscal_year_start) - 1, MONTH(_fiscal_year_start), 01 )

VAR _result = 
    UNION (
      ADDCOLUMNS (CALENDAR ( _today_date, _today_date),                                     "Timeframe", "Today",                      "Timeframe Order", 1 )
    , ADDCOLUMNS (CALENDAR ( _yesterday_date, _yesterday_date),                             "Timeframe", "Yesterday",                  "Timeframe Order", 2 )

    // Week
    , ADDCOLUMNS (CALENDAR ( _week_start - 7, _week_end - 7 ),                              "Timeframe", "Previous Week",              "Timeframe Order", 3 )
    , ADDCOLUMNS (CALENDAR ( _week_start, _week_end ),                                      "Timeframe", "Current Week",               "Timeframe Order", 4 )
    , ADDCOLUMNS (CALENDAR ( _week_start, _today_date ),                                    "Timeframe", "WTD",                        "Timeframe Order", 5 )
    , ADDCOLUMNS (CALENDAR ( _week_start_py, _today_date_py ),                              "Timeframe", "WTD Previous Year",          "Timeframe Order", 6 )

    // Month
    , ADDCOLUMNS (CALENDAR ( _previous_month_start, _previous_month_end ),                  "Timeframe", "Previous Month",             "Timeframe Order", 7 )
    , ADDCOLUMNS (CALENDAR ( _month_start, _month_end ),                                    "Timeframe", "Current Month",              "Timeframe Order", 8 )
    , ADDCOLUMNS (CALENDAR ( _month_start, _today_date ),                                   "Timeframe", "MTD",                        "Timeframe Order", 9 )
    , ADDCOLUMNS (CALENDAR ( _month_start_py, _today_date_py ),                             "Timeframe", "MTD Previous Year",          "Timeframe Order", 10 )

    // Quarter
    , ADDCOLUMNS (CALENDAR ( _previous_quarter_start, _previous_quarter_end ),              "Timeframe", "Previous Qtr",               "Timeframe Order", 11 )
    , ADDCOLUMNS (CALENDAR ( _quarter_start, _quarter_end ),                                "Timeframe", "Current Qtr",                "Timeframe Order", 12 )
    , ADDCOLUMNS (CALENDAR ( _quarter_start, _today_date ),                                 "Timeframe", "QTD",                        "Timeframe Order", 13 )
    , ADDCOLUMNS (CALENDAR ( _quarter_start_py, _today_date_py ),                           "Timeframe", "QTD Previous Year",          "Timeframe Order", 14 )

    // Financial Year
    , ADDCOLUMNS (CALENDAR ( _previous_fiscal_year_start, _previous_fiscal_year_end ),      "Timeframe", "Previous Fiscal Year",       "Timeframe Order", 15 )
    , ADDCOLUMNS (CALENDAR ( _fiscal_year_start_py, _today_date_py ),                       "Timeframe", "YTD Previous Fiscal Year",   "Timeframe Order", 16 )
    , ADDCOLUMNS (CALENDAR ( _fiscal_year_start, _fiscal_year_end ),                        "Timeframe", "Current Fiscal Year",        "Timeframe Order", 17 )
    , ADDCOLUMNS (CALENDAR ( _fiscal_year_start, _today_date ),                             "Timeframe", "YTD Fiscal",                 "Timeframe Order", 18 )
    , ADDCOLUMNS (CALENDAR ( _tomorrow_date, _fiscal_year_end ),                            "Timeframe", "Rest of Fiscal Year",        "Timeframe Order", 19 )

    // Calendar Year
    , ADDCOLUMNS (CALENDAR ( _previous_calendar_year_start, _previous_calendar_year_end ),  "Timeframe", "Previous Calendar Year",     "Timeframe Order", 20 )
    , ADDCOLUMNS (CALENDAR ( _previous_calendar_year_start, _today_date_py ),               "Timeframe", "YTD Previous Calendar Year", "Timeframe Order", 21 )
    , ADDCOLUMNS (CALENDAR ( _calendar_year_start, _calendar_year_end ),                    "Timeframe", "Current Calendar Year",      "Timeframe Order", 22 )
    , ADDCOLUMNS (CALENDAR ( _calendar_year_start, _today_date ),                           "Timeframe", "YTD Calendar",               "Timeframe Order", 23 )
    , ADDCOLUMNS (CALENDAR ( _tomorrow_date, _calendar_year_end ),                          "Timeframe", "Rest of Calendar Year",      "Timeframe Order", 24 )
    )

RETURN
_result