How to create columns to classify dates for "date over date to date" comparison of metrics?

[toulou]

I have a table with very few dimensions but 20+ metrics. I want to visualize the metrics in different ways. A few key scenarios:

- Month over Month to Date (e.g. comparing metric A Nov 1-10 2016 with Oct 1-10 2016). Obviously the November date range would always use the latest availble date in the Table within the Month as the MaxDate. 

- Month over Month LastYear to Date. Similar scenario as above only that I would compare Nov 1-10 2016 with Nov 1-10 2015. 

If I would just do this one or two metrics, it would be acceptable to create measurements for each of the scenario. However, with 20+ metrics this is probably not the route to go (there are also Week over Week, Year over Year or Day over Day (same year and last year) scenarios). 

I am wondering if there is a way to create a set of column (probably one each for year, month, week and day) in the table that would classify the inidividual dates.

For example:

Date	Metric 1	Metric 2	Metric 3	Metric 4	Metric 5	Metric 6	YtD Classification	MtD Classification	WtD Classification	Day Classification
11/10/2016	1	1	1	1	1	1	This Year	This Month	This Week	Last Day
10/10/2016	1	1	1	1	1	1	This Year	This Month	This Week	Previous Day
11/3/2016	1	1	1	1	1	1	This Year	This Month	Last Week	Other Day
10/9/2016	1	1	1	1	1	1	This Year	Previous Month	Other Week	Other Day
11/10/2015	1	1	1	1	1	1	Previous Year	Month Previous Year	Week Previous Year	Last Day Previous Year

The goal is to create those column values and use them as visual filters and enable them for multiple metrics then within the visual. 

Is this the right approach and if to how can it be accomplished? 

[GilbertQ · Accepted solution]

Hi @toulou, what about using a Period Table, which will make it easier to create the custom periods that you are after?

And then you can use your periods to easily create the calculations you require also?

http://community.powerbi.com/t5/Data-Stories-Gallery/Create-Dynamic-Periods-for-Fiscal-or-Calendar-D...

[toulou]

Thank you @GilbertQ. This is good solution. I have looked how you set this up and I going to apply this defining the relative dimensions that I need. 

I don't need PeriodType at the moment, however, I figuered this might be an interesting dimension also to indicate certain events in your Period Table. In my case this could be certain News Events for example that I then could also include as an additional insight. As a workflow I could set-up an Excel on SharePoint and have people adding events to a date table. This table could be added to the data model then and become a dimension for report and analysis. 

I haven't looked into the metric selector yet but I will. It is a nice way to reduce the number of visuals in a Report.

[GilbertQ]

Hi @toulou, glad that it helped out and also gave you some additional idea's

[v-qiuyu-msft]

Hi @toulou,

If I understand correctly, you want to create calculated columns to return YtD Classification, MtD Classification, WtD Classification and Day Classification, right?

From the sample you provided, I guess below DAX should meet your requirement:

YTD Classification = IF(YEAR('Table1'[Date])=YEAR(TODAY()),"This Year","Previous Year")

MtD Classification = Switch(TRUE(),
YEAR('Table1'[Date])=YEAR(TODAY()) && MONTH('Table1'[Date])=MONTH(TODAY()),"This Month",
YEAR('Table1'[Date])=YEAR(TODAY()) && MONTH('Table1'[Date])<MONTH(TODAY()),"Previous Month",
YEAR('Table1'[Date])<YEAR(TODAY()),"Month Previous Year")

WtD Classification = Switch( TRUE(),
WEEKNUM('Table1'[Date],1)=WEEKNUM(TODAY(),1) && YEAR('Table1'[Date])=YEAR(TODAY()),"This Week",
WEEKNUM('Table1'[Date],1)+1=WEEKNUM(TODAY(),1) && YEAR('Table1'[Date])=YEAR(TODAY()),"Last Week",
WEEKNUM('Table1'[Date],1)+1<WEEKNUM(TODAY(),1) && YEAR('Table1'[Date])=YEAR(TODAY()),"Other Week",
YEAR('Table1'[Date])<YEAR(TODAY()),"Week Previous Year")

Day Classification = Switch(TRUE(),
DATEADD('Table1'[Date],1,DAY)=TODAY() ,"Previous Day",
YEAR('Table1'[Date])=YEAR(TODAY()) && 'Table1'[Date]<TODAY(),"Other Day",
YEAR('Table1'[Date])<YEAR(TODAY()),"Last Day Previous Year")

If I misunderstand your requirement, please correct me.

Best Regards,
Qiuyun Yu

[BhaveshPatel]

Interesting Question. You probably need to discuss all the details of your data model as there can be many complexities in creating what you desired. Visit www.daxpatterns.com  to know more about these types of calculation.