Last year(previous year) YTD/QTD/MTD DAX formula when time table has data at weekly grain

[Tn_Gartn]

Hi All,

I am trying to calculate Last Year YTD,QTD& MTD. My data in the table is at the weekly grain.  i used following formula but this is giving me blank value.

Last year TOTALYTD =TOTALYTD(SUM(Sales[Sales]),SAMEPERIODLASTYEAR(DATESYTD('Time'[week])))

Last year TOTALQTD =TOTALQTD(SUM(Sales[Sales]),SAMEPERIODLASTYEAR(DATESQTD('Time'[week])))

Last year TOTALMTD =TOTALMTD(SUM(Sales[Sales]),SAMEPERIODLASTYEAR(DATESMTD('Time'[week])))

in this data set I am selecting week 10/20/2018 as a filter and expecting the sales number should be as below.

TOTAL YTD

2983

Totallast year YTD

1281

TOTAL QTD

288

TotalPYQTD

150

So for 10/20/2018 week last year week would be 10/21/2017 which is different than the simple time dimension.

Note: I am able to correctly calculate YTD,QTD,MTD  using 

TOTALYTD = TOTALYTD(SUM(Sales[Sales]),'Time'[week])

TOTALQTD = TOTALQTD(SUM(Sales[Sales]),'Time'[week])

TOTALMTD = TOTALMTD(SUM(Sales[Sales]),'Time'[week])

Appreciate your help in finding the right solution for this in advance!

Time table:

ROWWID	Week	PY Week
100120170107000	1/7/2017	1/2/2016
100120170114000	1/14/2017	1/9/2016
100120170121000	1/21/2017	1/16/2016
100120170128000	1/28/2017	1/23/2016
100120170204000	2/4/2017	1/30/2016
100120170211000	2/11/2017	2/6/2016
100120170218000	2/18/2017	2/13/2016
100120170225000	2/25/2017	2/20/2016
100120170304000	3/4/2017	2/27/2016
100120170311000	3/11/2017	3/5/2016
100120170318000	3/18/2017	3/12/2016
100120170325000	3/25/2017	3/19/2016
100120170401000	4/1/2017	3/26/2016
100120170408000	4/8/2017	4/2/2016
100120170415000	4/15/2017	4/9/2016
100120170422000	4/22/2017	4/16/2016
100120170429000	4/29/2017	4/23/2016
100120170506000	5/6/2017	4/30/2016
100120170513000	5/13/2017	5/7/2016
100120170520000	5/20/2017	5/14/2016
100120170527000	5/27/2017	5/21/2016
100120170603000	6/3/2017	5/28/2016
100120170610000	6/10/2017	6/4/2016
100120170617000	6/17/2017	6/11/2016
100120170624000	6/24/2017	6/18/2016
100120170701000	7/1/2017	6/25/2016
100120170708000	7/8/2017	7/2/2016
100120170715000	7/15/2017	7/9/2016
100120170722000	7/22/2017	7/16/2016
100120170729000	7/29/2017	7/23/2016
100120170805000	8/5/2017	7/30/2016
100120170812000	8/12/2017	8/6/2016
100120170819000	8/19/2017	8/13/2016
100120170826000	8/26/2017	8/20/2016
100120170902000	9/2/2017	8/27/2016
100120170909000	9/9/2017	9/3/2016
100120170916000	9/16/2017	9/10/2016
100120170923000	9/23/2017	9/17/2016
100120170930000	9/30/2017	9/24/2016
100120171007000	10/7/2017	10/1/2016
100120171014000	10/14/2017	10/8/2016
100120171021000	10/21/2017	10/15/2016
100120171028000	10/28/2017	10/22/2016
100120171104000	11/4/2017	10/29/2016
100120171111000	11/11/2017	11/5/2016
100120171118000	11/18/2017	11/12/2016
100120171125000	11/25/2017	11/19/2016
100120171202000	12/2/2017	11/26/2016
100120171209000	12/9/2017	12/3/2016
100120171216000	12/16/2017	12/10/2016
100120180106000	1/6/2018	1/7/2017
100120180113000	1/13/2018	1/14/2017
100120180120000	1/20/2018	1/21/2017
100120180127000	1/27/2018	1/28/2017
100120180203000	2/3/2018	2/4/2017
100120180210000	2/10/2018	2/11/2017
100120180217000	2/17/2018	2/18/2017
100120180224000	2/24/2018	2/25/2017
100120180310000	3/10/2018	3/11/2017
100120180407000	4/7/2018	4/8/2017
100120180414000	4/14/2018	4/15/2017
100120180421000	4/21/2018	4/22/2017
100120180428000	4/28/2018	4/29/2017
100120180505000	5/5/2018	5/6/2017
100120180512000	5/12/2018	5/13/2017
100120180519000	5/19/2018	5/20/2017
100120180526000	5/26/2018	5/27/2017
100120180602000	6/2/2018	6/3/2017
100120180609000	6/9/2018	6/10/2017
100120180616000	6/16/2018	6/17/2017
100120180623000	6/23/2018	6/24/2017
100120180630000	6/30/2018	7/1/2017
100120180707000	7/7/2018	7/8/2017
100120180714000	7/14/2018	7/15/2017
100120180721000	7/21/2018	7/22/2017
100120180728000	7/28/2018	7/29/2017
100120180804000	8/4/2018	8/5/2017
100120180811000	8/11/2018	8/12/2017
100120180818000	8/18/2018	8/19/2017
100120180825000	8/25/2018	8/26/2017
100120180901000	9/1/2018	9/2/2017
100120180908000	9/8/2018	9/9/2017
100120180915000	9/15/2018	9/16/2017
100120180922000	9/22/2018	9/23/2017
100120180929000	9/29/2018	9/30/2017
100120181006000	10/6/2018	10/7/2017
100120181013000	10/13/2018	10/14/2017
100120181020000	10/20/2018	10/21/2017

Sales table

TIMEROWWID	Sales Number
100120170107000	10
100120170114000	11
100120170121000	12
100120170128000	13
100120170204000	14
100120170211000	15
100120170218000	16
100120170225000	17
100120170304000	18
100120170311000	19
100120170318000	20
100120170325000	21
100120170401000	22
100120170408000	23
100120170415000	24
100120170422000	25
100120170429000	26
100120170506000	27
100120170513000	28
100120170520000	29
100120170527000	30
100120170603000	31
100120170610000	32
100120170617000	33
100120170624000	34
100120170701000	35
100120170708000	36
100120170715000	37
100120170722000	38
100120170729000	39
100120170805000	40
100120170812000	41
100120170819000	42
100120170826000	43
100120170902000	44
100120170909000	45
100120170916000	46
100120170923000	47
100120170930000	48
100120171007000	49
100120171014000	50
100120171021000	51
100120171028000	52
100120171104000	53
100120171111000	54
100120171118000	55
100120171125000	56
100120171202000	57
100120171209000	58
100120171216000	59
100120180106000	60
100120180113000	61
100120180120000	62
100120180127000	63
100120180203000	64
100120180210000	65
100120180217000	66
100120180224000	67
100120180310000	68
100120180407000	69
100120180414000	70
100120180421000	71
100120180428000	72
100120180505000	73
100120180512000	74
100120180519000	75
100120180526000	76
100120180602000	77
100120180609000	78
100120180616000	79
100120180623000	80
100120180630000	81
100120180707000	82
100120180714000	83
100120180721000	84
100120180728000	85
100120180804000	86
100120180811000	87
100120180818000	88
100120180825000	89
100120180901000	90
100120180908000	91
100120180915000	92
100120180922000	93
100120180929000	94
100120181006000	95
100120181013000	96
100120181020000	97

[v-chuncz-msft]

@Tn_Gartn,

You may try the measure below.

Measure =
VAR t =
    DATESQTD ( 'Time'[PY Week] )
RETURN
    CALCULATE ( SUM ( Sales[Sales] ), CONTAINSROW ( t, 'Time'[Week] ) )

[Tn_Gartn · Accepted solution]

Sam Thanks for the reply. I will try and let you know if it works. Meanwhile, I used following DAX For last year QTD and it calculates sales correctly.

Last YearQTD =

CALCULATE (
SUM ('Sale'[Sale]),
FILTER (
ALL ( 'Time'[Week]),
'Time'[Week] <= max('Time'[PY Week])
&& ROUNDUP( MONTH('Time'[Week])/3,0)=ROUNDUP( MONTH(max('Time'[PY Week]))/3,0) && YEAR ( 'Time'[week] ) = YEAR (max('Time'[PY Week]))))