Compare value from 364 days ago

[jpt1228]

Hello, I have data that is reported every 28 days or 13 times per year. I need to compare the current value with the previous value from last year (reported 364 days prior). Since the calendar dates are not the same: 5/21/2017 VS 5/22/2016 I can't compare months or the week in the year because some of the weeks fall on different weeks in the year. I think I need to calculate the current date which in this case is 5/21/2017 and compare that value to the one reported on 5/22/2016. Since the data is reported every 28 days I just need to take the MAX date and the date from 364 days before.

I have a DIMdate table and fact table. The fact table is sturucture with a customer, TDP value, and date.

Desired Result Based on the MAX date to always show the most up to date. Current data would be 5/22/17 VS 5/22/17-364 days. The next data release would update to show 6/18/2017 and then the value from 6/19/2016 (364 days before)

Customer	TDP LY	TDP TY	TDP CHG
Customer A	140	146	6
Customer B	521	541	20

I tried this formula but it doesn't bring in the value I am looking for.

LY TDP = CALCULATE(SUM(TDP[TDP]),
   DATEADD(DIMDate[Date],-364,DAY))

[OpenDataLab]

The easiest way I have found to do this is by adding a day overall number, basically a unique row id 1,2,3,4,5 etc....I use this to navigate back a froward through my date range.

LY TDP =
CALCULATE (
    SUM ( TDP[TDP] ),
    FILTER (
        ALL ( 'Date' ),
        'Date'[Day Over All]
            = MAX ( 'Date'[Day Over All] ) - 364
    )
)

[jpt1228]

Hi @OpenDataLab - Are you saying bring in the day of the year for each date in the DateDim 1-365 not including a leap year?

Thanks

[OpenDataLab]

No.  Just add an identity field that keeps on going from the first day of your date dimension until the last day. e.g if your date dimension had 2 years worth of data then you would hve values from 1 through to 728.

[jpt1228]

I have multiple dates for multiple customers. Im pretty new to PoweerBI. Would this be an index column of the DimDate table? This is a sample of my source table. I have transformed the Time Description into an actual date.

Time Description	Geography	Brand	TDP
Quad End 2015-Aug-16	TOTAL US - SPECIALTY GOURMET CHANNEL	Company A	117
Quad End 2015-Aug-16	TOTAL US - MULO	Company A	19
Quad End 2015-Aug-16	TOTAL US - NATURAL CHANNEL	Company B	14
Quad End 2015-Aug-16	TOTAL US - MULO	Company B	1
Quad End 2015-Aug-16	TOTAL US - SPECIALTY GOURMET CHANNEL	Company C	5
Quad End 2015-Aug-16	TOTAL US - MULO	Company C	2
Quad End 2015-Dec-06	TOTAL US - SPECIALTY GOURMET CHANNEL	Company A	118
Quad End 2015-Dec-06	TOTAL US - MULO	Company A	20
Quad End 2015-Dec-06	TOTAL US - NATURAL CHANNEL	Company B	18
Quad End 2015-Dec-06	TOTAL US - MULO	Company B	0
Quad End 2015-Dec-06	TOTAL US - SPECIALTY GOURMET CHANNEL	Company C	5
Quad End 2015-Dec-06	TOTAL US - MULO	Company C	2
Quad End 2015-Jul-19	TOTAL US - SPECIALTY GOURMET CHANNEL	Company A	116
Quad End 2015-Jul-19	TOTAL US - MULO	Company A	18
Quad End 2015-Jul-19	TOTAL US - NATURAL CHANNEL	Company B	16
Quad End 2015-Jul-19	TOTAL US - MULO	Company B	1
Quad End 2015-Jul-19	TOTAL US - SPECIALTY GOURMET CHANNEL	Company C	5
Quad End 2015-Jul-19	TOTAL US - MULO	Company C	2
Quad End 2015-Nov-08	TOTAL US - SPECIALTY GOURMET CHANNEL	Company A	117