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 snip to help explain the issue. I think the visual snip is a better explanation than the worded explanation above. Pelase provide an M code or dax code to help me solve this issue! Thank you in advance!

 

1 Reply

  • AlB's avatar
    AlB
    Community 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