cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
norway84 Frequent Visitor
Frequent Visitor

Showing date, month, quarter and year on columns and measures on rows

Hi,

 

I guess this is a common thing which has been answered many times before but I'm not able to find an solution (probably because I don't know what to search for) so I'm very glad if somebody could point me to right directon.

 

I want to make an table as illustrated below. On the rows I want different measures and on the colums I want to see the value of the measures on the date, month, quarter and year level. I know how to write a measure showing month to date, quarter to date and year to date but I don't understand how I'm then able to get the measures formatted as illustrated below.

image.png

 I have a date table in my model as shown below.

 

image.png

 

1 ACCEPTED SOLUTION

Accepted Solutions
norway84 Frequent Visitor
Frequent Visitor

Re: Showing date, month, quarter and year on columns and measures on rows

I was a litte too fixated on having my date hierchy on the columns so I didn't realize it was a fairly easy solution using switch. 

 

1. I made a new table with one column named ReportPeriod containing the values Year, Quarter, Month, Week and Day 

 

2. Made a dynamic measure based on the value of ReportPeriod

 

E.g:

 

SalesReport =
IF (
HASONEVALUE ( ReportPeriod[Period] );
SWITCH (
VALUES ( ReportPeriod[Period] );
"Year"; [SalesYear];
"Quarter"; [SalesQuarter];
"Month"; [SalesMonth];
"Week"; [SalesWeek];
"Day"; [Sales];
BLANK ()
);
BLANK ()
)

1 REPLY 1
norway84 Frequent Visitor
Frequent Visitor

Re: Showing date, month, quarter and year on columns and measures on rows

I was a litte too fixated on having my date hierchy on the columns so I didn't realize it was a fairly easy solution using switch. 

 

1. I made a new table with one column named ReportPeriod containing the values Year, Quarter, Month, Week and Day 

 

2. Made a dynamic measure based on the value of ReportPeriod

 

E.g:

 

SalesReport =
IF (
HASONEVALUE ( ReportPeriod[Period] );
SWITCH (
VALUES ( ReportPeriod[Period] );
"Year"; [SalesYear];
"Quarter"; [SalesQuarter];
"Month"; [SalesMonth];
"Week"; [SalesWeek];
"Day"; [Sales];
BLANK ()
);
BLANK ()
)