Matrix with "Period to Date" rows

[pengbsam0830]

Hello Experts:
    Looking for some help related to how to structure the Matrix report in the below information.

1. Report dataset
   1. is a single dataset with all needed attributes 
   2. A DATE_DIM table
2. The report parameter is a single date.
3. Report Column has different type of transactions (ORDER, SHIPMENT, INVOICE)
   1. Report Colume also Split between current vs Last year
4. Report Row is by DIVISION/BRAND. Assuming multiple Brands per division.
5. In addition to the Value per selected parameter (Single date), the user also wants to see additional rows for "Week to Date", "Month To Date", "Year To Date"

   I have #1,2,3,4 completed without issue, but struggling with 5.  

   • I Can use DAX to calculate another measurement for MTD, WTD, QTD, YTD. But it  is another measure which can't be added to the row. 
   • I can use a date range (1st of the year to "Parameter Date"), but don't know how to filter this by row per division / Brand. 

     Appreciate any suggestion you may have. Thank you.

     

[aduguid]

You could use the following DAX query for the timeframes. Then you could use a column grouping for the transaction type. I've added the column "Year" to the timeframe query for current and last year used as a column grouping.

Total Amount = CALCULATE(SUM('YourTable'[Amount]))+ 0

Calendar Timeframe = 
VAR _today_date =                   TODAY()
VAR _yesterday_date =               _today_date - 1
VAR _calendar_year =                YEAR(_today_date)
VAR _fiscal_year =                  YEAR(EDATE( _today_date, 6))
VAR _fiscal_year_start =            DATE( _fiscal_year - 1, 07, 01)
VAR _quarter_start =                DATE ( YEAR (_today_date), ROUNDUP ( DIVIDE ( MONTH (_today_date), 3 ), 0 ) * 3 - 2, 1 )
VAR _month_start =                  DATE( YEAR(_today_date), MONTH(_today_date), 01 )
VAR _week_start =                   _today_date - WEEKDAY ( _today_date, 2 )
VAR _calendar_year_start =          DATE( _calendar_year , 01, 01)

VAR _result = 
    UNION (
      ADDCOLUMNS (CALENDAR ( _today_date, _today_date),             "Timeframe", "DATE",                "Timeframe Order", 1, "Year", "CURRENT YEAR")
    , ADDCOLUMNS (CALENDAR ( _week_start, _today_date ),            "Timeframe", "WEEK TO DATE",        "Timeframe Order", 2, "Year", "CURRENT YEAR")
    , ADDCOLUMNS (CALENDAR ( _month_start, _today_date ),           "Timeframe", "MONTH TO DATE",       "Timeframe Order", 3, "Year", "CURRENT YEAR")
    , ADDCOLUMNS (CALENDAR ( _quarter_start, _today_date ),         "Timeframe", "QUARTER TO DATE",     "Timeframe Order", 4, "Year", "CURRENT YEAR")
    , ADDCOLUMNS (CALENDAR ( _fiscal_year_start, _today_date ),     "Timeframe", "YEAR TO DATE",        "Timeframe Order", 5, "Year", "CURRENT YEAR")
    , ADDCOLUMNS (CALENDAR ( _today_date - 365, _today_date - 365),            "Timeframe", "DATE",                "Timeframe Order", 1, "Year", "LAST YEAR")
    , ADDCOLUMNS (CALENDAR ( _week_start - 365, _today_date - 365),            "Timeframe", "WEEK TO DATE",        "Timeframe Order", 2, "Year", "LAST YEAR")
    , ADDCOLUMNS (CALENDAR ( _month_start - 365, _today_date - 365),           "Timeframe", "MONTH TO DATE",       "Timeframe Order", 3, "Year", "LAST YEAR")
    , ADDCOLUMNS (CALENDAR ( _quarter_start - 365, _today_date - 365),         "Timeframe", "QUARTER TO DATE",     "Timeframe Order", 4, "Year", "LAST YEAR")
    , ADDCOLUMNS (CALENDAR ( _fiscal_year_start - 365, _today_date - 365),     "Timeframe", "YEAR TO DATE",        "Timeframe Order", 5, "Year", "LAST YEAR")
    //, ADDCOLUMNS (CALENDAR ( _fiscal_year_start, _today_date ),     "Timeframe", "FISCAL YEAR TO DATE", "Timeframe Order", 5)  // if you need fiscal year to date
    )

RETURN
_result

You also may want to use another DAX query for the transaction types

Transaction Types = 
VAR _results = 
    UNION (
      ROW ("Transaction_Type", "ORDER",		"Transaction_Type_Order", 1)
    , ROW ("Transaction_Type", "SHIPMENT",	"Transaction_Type_Order", 2)
    , ROW ("Transaction_Type", "INVOICE",	"Transaction_Type_Order", 3)
    )

RETURN
_results

[lbendlin]

Read about "Switch values to rows".

[pengbsam0830]

It's a nice option, but it makes all measures to rows? That's not what I am looking for.