Forum Discussion

Nograd's avatar
Nograd
Regular Visitor
8 years ago
Solved

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[I...
  • Nograd's avatar
    Nograd
    8 years ago

    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])
    )
    )

Nograd's avatar
Nograd
Regular Visitor
8 years ago

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])
)
)