Grouping matrix columns using custom measures

[DylanSandry]

Hello,

I've been destroying myself trying to figure this out and I wonder if someone can help.

I have the following table (Simplified to get to the root of my problem):

Job	Product	Price	SaleDate
1	A	$100	01062024
1	B	$200	01062024
2	A	$110	19062024
3	C	$300	18062024
4	A	$120	01052024

I would like to display it as follows:

	Today		ThisMonth		ThisYear
Product	TotalPrice	TotalCount	TotalPrice	TotalCount	TotalPrice	TotalCount
A	$110	1	$210	2	$330	3
B	0	0	$200	1	$200	1
C	0	0	$300	1	$200	1

I can flatten the table and created individual measures for every combination of column (e.g. TodayTotalPrice and TodayTotalCount), but I don't want a flat table. I want the nicely structured matrix that can be collapsed and expanded.

If anyone could please help for this simple example that would be great. I'm confident I'll be able to expand it to my larger dataset.

Thanks.

[DylanSandry]

That's worked perfectly thanks!

Just a follow up question. Is it possible to show and hide certail values based on matrix columns? I want to also add averages after the TotalPrice and TotalCount sections, so it will look like this

	Today		ThisMonth		ThisYear		MonthAverage		YearAverage
Product	TotalPrice	TotalCount	TotalPrice	TotalCount	TotalPrice	TotalCount	PricePerDay	CountPerDay	PricePerDay	CountPerDay

I can create the second section of matrix columns with a couple new measures and altered switch statements, but how can I hide the values that dont correspond to that section of the table? (Hide TotalPrice, TotalCount from the MonthAverage and YearAverage sections and vice versa).

Cheers.

[Daniel29195]

@DylanSandry 

do the following : 
create a custom table :  ( No need for the Column "Column")

add column1 to the matrix. 

and now you just need to create 2 measures : 

TotalPrice

 and 

TotalCount

for each measure, use the switch statement :
switch(

true ( ),

   selectedvalue(table[column1]) =  "Today" , calculate( [total price] , date =  today () ,

selectedvalue(table[column1]) =  "This Month" , calculate( [total price] , year(sales[order date]) = year ( today() ,  month(sales[order date]) = month( today()) ,

.........., ,

let me know if this helps.