Forum Discussion
GROUP BY and COUNT
I am trying to group the following data set based on three dimensions: "current year", "last year", "past years" (past years also contains last year).
| Date |
| 01.01.2019 |
| 01.05.2019 |
| 01.01.2020 |
01.01.2021 |
Now, I want to count the dates which fit the dimensions:
| Dimension | Count |
| Current year | 1 |
| Last year | 1 |
| Past years | 3 |
1) How can I generate the summary (GROUPBY or SUMMARIZE or Power Query)?
2) How can I make sure, that in case of missing date (e.g. Last year has no dates) the dimension still appears with count 0?
kiril see the attached solution, tweak it as you see fit.
Check my latest blog post Compare Budgeted Scenarios vs. Actuals I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!
⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡
2 Replies
- vanessafvg
Community Champion
you can add +0 to your measure
or you can check for blanks,
measure name = if(isblank(measure),0, measure) which will bascially to check if its blank and if so make it 0.
However that might not work for all scenarios. If you want to use time intelligence, you need to use a date table with continues values (ie no missing values), you can also then join to that date table your date and can return where there is no date.
So join your date field twith a relationship to your date table and then pull the date back from the date table with the measure that checks for blank or returns a +0
hope that makes sense.
- parry2k
Super User
kiril see the attached solution, tweak it as you see fit.
Check my latest blog post Compare Budgeted Scenarios vs. Actuals I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!
⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡
