Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 |
Solved! Go to 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]))))
You may try the measure below.
Measure = VAR t = DATESQTD ( 'Time'[PY Week] ) RETURN CALCULATE ( SUM ( Sales[Sales] ), CONTAINSROW ( t, 'Time'[Week] ) )
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]))))
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
95 | |
80 | |
68 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |