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.

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.

Quarter organization | Microsoft Fabric Community

4 Replies

aduguid

Memorable Member

2 years ago

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

Anonymous

Not applicable

2 years ago

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

Memorable Member

2 years ago

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

Anonymous
Not applicable
2 years ago
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.
Quarter organization.pbix52 KB

Forum Discussion

Quarter organization

4 Replies

Related content

Quarter over Quarter calculation - Display Quarters missing

Calculating Quarter over Quarter Change

Quarter calculation upto selected month

Ordering Data by Quarters

Last 3 quarter