Forum Discussion
New Measures - Weekly Current Year, Weekly Last Year, YTD Weekly Current and YTD Weekly Last Year
Hi there, I have a transaction list. It runs on daily basis. I have it summarized by week ending #. Something like this: Ticket Number | Date | Amount | Week Ending # It could have mult...
v-lionel-msft
Community Support
Community SupportHi Anonymous ,
Maybe you can do like this:
Weekly Current Year Amount =
CALCULATE(
SUM(Sheet1[Amount]),
FILTER(
ALL(Sheet1),
Sheet1[Year] = SELECTEDVALUE( Sheet1[Year]) && Sheet1[Week] = SELECTEDVALUE(Sheet1[Week])
)
)Weekly Last Year Amount =
CALCULATE(
SUM(Sheet1[Amount]),
FILTER(
ALL(Sheet1),
Sheet1[Year] = SELECTEDVALUE(Sheet1[Year]) - 1 && Sheet1[Week] = SELECTEDVALUE(Sheet1[Week])
)
)YTD Weekly Current Year Amount =
CALCULATE(
SUM(Sheet1[Amount]),
FILTER(
ALLSELECTED(Sheet1),
Sheet1[Year] = SELECTEDVALUE(Sheet1[Year]) && Sheet1[Week] <= SELECTEDVALUE(Sheet1[Week])
)
)YTD Weekly Last year Amount =
CALCULATE(
SUM(Sheet1[Amount]),
FILTER(
ALLSELECTED(Sheet1),
Sheet1[Year] = SELECTEDVALUE(Sheet1[Year])-1 && Sheet1[Week] <= SELECTEDVALUE(Sheet1[Week])
)
)
Best regards,
Lionel Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Lionel,
Thank you for your suggestions. The YTD ones are giving me blanks.
Jen
- v-lionel-msft
Hi Anonymous ,
It may be caused by the difference in our data model. In addition, did you add the ALLSELECTED() function to your formula?
Best regards,
Lionel ChenIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.