Forum Discussion

npals's avatar
npals
Frequent Visitor
6 years ago

Summarizing Counts Per Month/Year

I am looking for a way to summarize a table of issues with listed dates created/closed, into a table of the month/year with counts of issues created/closed per month over time.  I have attached a sni...
AlB's avatar
AlB
Icon for Community Champion rankCommunity Champion
6 years ago

Hi npals 

1. Create a date table with a column with the Month-Year format as shown.

2. Create an active 1-to-many relationship between DateTable[Date] and Table1[Date Created]

3. Create an inactive 1-to-many relationship between DateTable[Date] and Table1[Date Closed]

4.Place DateTable[Month-Year] in the rows of a table visual

5. Create these measures and place them in the table visual:

Count_created = COUNT(Table1[Date Created]) // Uses the active relationship
Count_closed =
CALCULATE (
    COUNT ( Table1[Date Closed] ),
    USERELATIONSHIP ( DateTable[Date], Table1[Date Closed] )//// Activates the  inactive relationship
)

Please mark the question solved when done and consider giving kudos if posts are helpful.

 Cheers