GROUP BY and COUNT

[kiril]

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?

[parry2k · Accepted solution]

@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.⚡

[vanessafvg]

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.