3 Month YoY comparison

[Ria]

My goal is to create a table similar to the visualization matrix below.  This works great except when the 3 month period straddles 2 differenct years.  (December, January, February).   I've tried to recreate this with the PreviousMonths function, but still have the same issue and have the additional problem of having the previous month next to the current month and not separated as below.  Do I need to create separate measures for each of current month, current month minus 1 month, minus 2 months etc.? and how? I'm new to DAX Time Intelligence functions and with a lot of help from past postings have created a Dates table and the Prior Month Sales, but now am stuck.

	2015			2015 Total	2016			2016 Total	Grand Total
	February	March	April		February	March	April
Customer A	93	134	139	366	88	78	82	248	614
Customer B	52	55	94	202	65	53	93	211	413
Customer C	138	113	14	265					265
TOTAL	283	302	247	832	153	131	175	459	1,291

[v-sihou-msft]

@Ria

I assume you have fact data per Customer per Month in the fact table. If you just want to display a matrix, you just need to have month group on Year in Column Group of the matrix, then use a slicer to filter expected 3 months.

If you want to calculate the PreviousMonth, Current Month - 2 month Total. You can just use PARALLELPERIOD() in your calculation. 

Measure = CALCULATE(SUM('Table'[Amount]),PARALLELPERIOD('Table'[Date],-1,MONTH))

Regards,

[GilbertQ]

Hi @Ria

What about creating a calculated measure with the following below:

YOY = CALCULATE([Measure],SAMEPERIODLASTYEAR('Date'[Calendar Date']))

What this will do, is based on which ever date period you put into your table, it will go to the same period of the previous years and subsequent years.

One thing to note is that you MUST put in the Date columns from your Date table for this to work as expected.

And then put in this measure above into your data as shown below?

[Ria]

This gives me the information I am looking for but is it possible to change the presentation?

Currently:

MonthInCalendar                                                 Dec 16                                   Jan 17                          Feb 17                Total

Customer                                                     PR YR       SALES                     PR YR      SALES                PR YR    SALES      PR YR   SALES

A                                                                   82               96                         85              133                  87          113         254      342     

(Rows:   Customer.   Columns:  MonthInCalendar.  Values:  PR YR, Sales.)

___________________________________________________________________________________________________________________________________________

Requested Presentation:                  Dec 15           Jan 16       Feb 16  Total     Dec 16     Jan 17     Feb 17    Total

Customer A                                        82                    85            87        254        96            133          113        342

[v-sihou-msft]

@Ria

No, it's not possible have lengend group on measures, you can only have measures group on legend.

Regards,

[Ria]

This gives me the information I am looking for, but if possible is there a way to change the presentation.

Currently:

MonthInCalendar

[Phil_Seamark]

Can you please post a sample of your data so we can help creating the measures.

It looks doable. 🙂