Count occurrences with date hierarchy

[Anonymous]

I have a spreadsheet that marks when an organization was contacted. For example 11/12/21, 11/28/21, 1/5/22, 1/6/22, and 1/9/22. How do put in a date hierarchy AND get powerbi to count how many times a month an organization was contacted? I would need the output to be 2 in November 2021 and 3 in January 2022. I have found ways to do one or the other, but not both. 

I am new to PowerBI. Any help is appreicated.  

[v-zhangti · Accepted solution]

Hi, @Anonymous 

You can try the following methods. Create a new calendar table.

Table:

Date = CALENDAR(MIN('Table'[contact date]),MAX('Table'[contact date]))

Column:

Month Year = Format([date],"mmm-yyyy")

Measure:

Count =
CALCULATE (
    COUNT ( 'Table'[contact date] ),
    FILTER ( ALL ( 'Date' ), [Month Year] = SELECTEDVALUE ( 'Date'[Month Year] ) )
)

Is this the result you expect?

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@Anonymous , You can create a date table with month, year, qtr etc., and join it with the date of your table and if needed you can create a custom hierarchy.

or you can use the filter from the date table

Addcolumns(calendar(date(2020,01,01), date(2021,12,31) ), "Month no" , month([date])
, "Year", year([date])
, "Month Year", format([date],"mmm-yyyy")
, "Month year sort", year([date])*100 + month([date])
, "Month",FORMAT([Date],"mmmm")
, "Month sort", month([DAte])

)