Easy question? Possibly? - group by date and category

[aaa12]

Hello - I tried several methods but just could not get the right answer for the follwing issue. Any suggestions are appreciated and thank you in advance.

Take this:

and group by month/year and Environment... EITHER one of the options below would work:

[v-jingzhan-msft]

Hi @aaa12 

Option 1: add a new column in the same table

Culm_Count = 
COUNTROWS(
    FILTER('Table (3)',
        'Table (3)'[Environment]=EARLIER('Table (3)'[Environment]) && 
        YEAR('Table (3)'[Created_at])=YEAR(EARLIER('Table (3)'[Created_at])) &&
        MONTH('Table (3)'[Created_at])=MONTH(EARLIER('Table (3)'[Created_at]))
    )
)

EARLIER function (DAX) - DAX | Microsoft Learn

Option 2: add a new table

Table 4 = 
SUMMARIZE(
    ADDCOLUMNS('Table (3)',"Created_YearMonth",FORMAT('Table (3)'[Created_at],"mmm-yy")),
    [Created_YearMonth],
    'Table (3)'[Environment],
    "Count",COUNTROWS('Table (3)')
)

Best Regards,
Jing
If this post helps, please Accept it as Solution to help other members find it. Appreciate your Kudos!

[aaa12]

thank you for the response. My sincere apologies because I had posted a wrong view of the output. I have updated my original post.

The actual results should be what you see below....an aggregate running count by month by month. Ex: on feb-24, A shows 4 because it occured once in Jan-24 and 3 times in Feb-24. Is there a solutions for this?

[v-jingzhan-msft · Accepted solution]

Hi @aaa12 

Yes. For a new column:

Culm_Count = 
COUNTROWS(
    FILTER('Table (3)',
        'Table (3)'[Environment]=EARLIER('Table (3)'[Environment]) && 
        'Table (3)'[Created_at]<=EARLIER('Table (3)'[Created_at])
    )
)

You can also try measures if you want to display the results in a visual finally. Here is a blog for your reference: Computing running totals in DAX - SQLBI

Best Regards,
Jing
If this post helps, please Accept it as Solution to help other members find it. Appreciate your Kudos!

[aaa12]

summoning top contrubutors... @rajendraongole1 ; @foodd @amitchandak . Any one of you worked on a similar use case before? And if so, could you recommend a path forward?

[aduguid]

Are you using a calendar table with a relationship to your 'Created_at' date?

[aaa12]

I am using created_at field in the table but I have a calendar date table and I can join created_at to the date field, if it will lead to a solution.

[aduguid]

If you have a month date in your calendar, you can use this to as a row group as long as there is a relationship to your created_at date from your calendar.

[aaa12]

I have tried that approach before but it didn't work. Tried once again after your suggestion and once again, it did not work, unfortunately. Is there a path to solution not using the calendar table?