Measure to Group Data

[Anonymous]

Hi, 

I am using a Tabular live connection, and I need to group my stock value into Seasons. 

If it was imported data I would just add a column like below, but how do I get that to work in a measure?

IF(MONTH([Last Invoice Date])<7;"SS"&YEAR([Last Invoice Date]);"AW"&year([Last Invoice Date])))

Then I would use this measure as columns in a Matrix.

I hope it makes sense what I am asking for. 

Thanks, 

[Greg_Deckler]

You need to wrap an aggregation function around your columns like MAX, MIN, SUM, FIRSTNONBLANK, LASTNONBLANK, AVERAGE, etc.

[Anonymous]

Hi,

It does not work. It only returns AW19. Furthermore, I can only add this to values, I need it in Columns of my Matrix. 

Season = MAX([Last Invoice Date];IF(MONTH([Last Invoice Date])<7;"SS"&YEAR([Last Invoice Date]);"AW"&year([Last Invoice Date])))

I need to create below Matrix. 

I do not know if it matters that Last Invoice Date is a measure. 

Thanks, 

[nhoward]

Hi,

Measures are only meant for values.  

Your seasons are dimensions.  Assuming you have a Calendar Table, you need a column in there, that shows the Season that each day belongs to.  You can then use that column as a column in your Matrix.   It would work in a similar way to having Quarters in your calendar. 

Hope that helps. 

[Anonymous]

Hi nhoward, 

It is in a live connection so I cannot do that. I need it to be a measure, I cannot add tables. 

Maybe it is just not possble. 

[nhoward]

I'm fairly sure that you can import from another source, even though most of your tables are live connections. 

You do need a master calendar, if you need to group/summarise by periods.