Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Count occurrences with date hierarchy

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.  

Need Help
Tips and Tricks
  • v-zhangti's avatar
    v-zhangti
    4 years ago

    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.

2 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    4 years ago

    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])

    )

  • v-zhangti's avatar
    v-zhangti
    Icon for Community Support rankCommunity Support
    4 years ago

    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.