Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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 multiple transactions every day in the week.
I am trying to create a few measures to capture:
Weekly Current Year Amount
Weekly Last Year Amount
YTD Weekly Current Year Amount
and YTD Weekly Last year Amount
I have tried a few coding... doesn't seem to work...
Please advise.
Jen
Hi @Anonymous ,
Do you want to create the following four measures?
Please give example data for all used tables.
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.
Hi @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
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 Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@Anonymous there are tons of posts on time intelligence, did you searched and tried?
Here is a link to few:
https://www.red-gate.com/simple-talk/sql/bi/creating-time-intelligence-functions-in-dax/
I would 💖 Kudos 🙂 if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
Those are informative posts.
My question is more on what to do with Weekly measures. The YTD, QTD or MTD work fine for me. Given WTD or week to date functions are not available in Power BI, I need further guidence.
Thank you
Jen
@Anonymous there are tons of resources on WTD as well, here is a link to one
https://www.youtube.com/watch?v=jBPLm5BHl2c
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
Thank. I think that I asked the question the wrong way.
Tonnes Last Year | Tonnes This Year | Tonnes Last Year Cumulative | Tonnes This Year Cumulative | |
Week 1 | 250 | 238 | 250 | 238 |
Week 2 | 300 | 526 | 550 | 764 |
Week 3 | 125 | 114 | 675 | 878 |
Week 4 | 382 | 538 | 1057 | 1416 |
Week 5 | 405 | 410 | 1462 | 1826 |
Week 6 | 508 | 235 | 1970 | 2061 |
Week 7 | 137 | 233 | 2107 | 2294 |
I am trying to create measure for the cumulative columes.
I have following ...
Tonnes 2020 cumulative = TotalYTD(sum('data table'[Tonnes]),'data table'[date],if('data table'[Year]=2020,2020,0))
It's returning weekly numbers by the week. Not YTD culmulative.
Appreciate your help as always.
Jen