Matrix with "Period to Date" rows

pengbsam0830 — Sun, 28 Apr 2024 21:19:41 GMT

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

Report dataset
1. is a single dataset with all needed attributes
2. A DATE_DIM table
The report parameter is a single date.
Report Column has different type of transactions (ORDER, SHIPMENT, INVOICE)
1. Report Colume also Split between current vs Last year
Report Row is by DIVISION/BRAND. Assuming multiple Brands per division.
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.

Re: Matrix with "Period to Date" rows

lbendlin — Sun, 28 Apr 2024 23:22:38 GMT

Read about "Switch values to rows".

Re: Matrix with "Period to Date" rows

aduguid — Mon, 29 Apr 2024 04:54:10 GMT

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

Re: Matrix with "Period to Date" rows

pengbsam0830 — Mon, 29 Apr 2024 01:11:48 GMT

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

topic Matrix with "Period to Date" rows in DAX Commands and Tips

Matrix with "Period to Date" rows

Re: Matrix with "Period to Date" rows

Re: Matrix with "Period to Date" rows

Re: Matrix with "Period to Date" rows