Forum Discussion
Get the Prior Year Week Top Item
Using the following DAX but does not work if the Top Item is not in the current year week, not sure how to get distinct items for same week last year?
Top 1 Item PY =
CALCULATE(DISTINCT(Sheet1[Item]),
FILTER(TOPN ( 1,
DISTINCT(Sheet1[Item]),[Sls Wkly PY], DESC)
,TRUE()
)
)
Top 1 Sum PY = CALCULATE(
[Sls Wkly PY],
FILTER(TOPN ( 1, DISTINCT(Sheet1[Item]),[Sls Wkly PY], DESC)
,TRUE()
)
)
The Top Item for "Prior Week Last Year" should be Pizza but Pizza is not in week 02 for current year. Also the formula should fill in weeks 03 and 04 for the prior year week since data is in the fact table for those weeks. Note: have a date dimension table.
Was able to figure it out, please see below. Thanks for your help.
Top 1 Sum PY5 =
CALCULATE(
TOPN(1, SUMMARIZE(Sheet1, Sheet1[Cost]),
CALCULATE(SUM(Sheet1[Cost]))
),
FILTER(ALL(Dim_Date),
Dim_Date[WkEndng Yr] = VALUES (Dim_Date[WkEndng Yr]) - 1
&& CONTAINS(VALUES (Dim_Date[WkEndng YrDyNmbr]),
Dim_Date[WkEndng YrDyNmbr],
Dim_Date[WkEndng YrDyNmbr])
)
)Top 1 Item PY5 =
CALCULATE(
TOPN(1,
SUMMARIZE(Sheet1, Sheet1[Item]),
CALCULATE(SUM(Sheet1[Cost]))
),
FILTER(ALL(Dim_Date),
Dim_Date[WkEndng Yr] = VALUES (Dim_Date[WkEndng Yr]) - 1
&& CONTAINS(VALUES (Dim_Date[WkEndng YrDyNmbr]),
Dim_Date[WkEndng YrDyNmbr],
Dim_Date[WkEndng YrDyNmbr])
)
)
3 Replies
Hi,
Does it help if you replace DISTINCT() with VALUES(). If it does not, share the link from where i can download your PBI file.
Tried both functions and gives same results.
Link to file: https://drive.google.com/open?id=140hkRtFx-Akz-bZDD4E255AIkaFEEwAU
Was able to figure it out, please see below. Thanks for your help.
Top 1 Sum PY5 =
CALCULATE(
TOPN(1, SUMMARIZE(Sheet1, Sheet1[Cost]),
CALCULATE(SUM(Sheet1[Cost]))
),
FILTER(ALL(Dim_Date),
Dim_Date[WkEndng Yr] = VALUES (Dim_Date[WkEndng Yr]) - 1
&& CONTAINS(VALUES (Dim_Date[WkEndng YrDyNmbr]),
Dim_Date[WkEndng YrDyNmbr],
Dim_Date[WkEndng YrDyNmbr])
)
)Top 1 Item PY5 =
CALCULATE(
TOPN(1,
SUMMARIZE(Sheet1, Sheet1[Item]),
CALCULATE(SUM(Sheet1[Cost]))
),
FILTER(ALL(Dim_Date),
Dim_Date[WkEndng Yr] = VALUES (Dim_Date[WkEndng Yr]) - 1
&& CONTAINS(VALUES (Dim_Date[WkEndng YrDyNmbr]),
Dim_Date[WkEndng YrDyNmbr],
Dim_Date[WkEndng YrDyNmbr])
)
)
