Counting number of occurences of certain word in column for each date.

[Anonymous]

Hi everyone,

This seems quite easy but I'm struggling so here it goes.

This is my table, showing the status of certain Job in a certain date. In terms of dates I have the last day for previous months and the MAX date for current month (current month equals the month we are living on right now):

So, what I want is an evolution of the number of jobs for each [StatusDescription] for the last 12 months .

Visually it seems like this:

I'm doing this in PowerBI but for some reason I'm not getting any result.

Note: I want the last 12 months from Today's date.

[v-angzheng-msft · Accepted solution]

Hi, @Anonymous 

Try to create a measure like this:

_Allselected_filter_first12 =
VAR _table =
    FILTER (
        ALLSELECTED ( 'Table' ),
        'Table'[StatusDescription] = MAX ( 'Table'[StatusDescription] )
            && EOMONTH ( 'Table'[Date], 0 ) <= EOMONTH ( TODAY (), 0 )
            && EOMONTH ( 'Table'[Date], 0 ) > EOMONTH ( TODAY (), -12 )
    )
VAR _count12 =
    IF ( COUNTROWS ( _table ) = 12, CALCULATE ( COUNT ( 'Table'[word] ), _table ) )
RETURN
    IF ( HASONEVALUE ( 'Table'[Date] ), _count12, COUNT ( 'Table'[word] ) )

Or, please share your sample pbix file's link here, then I can try to look into it to come up with a more accurate measure.

Hope this helps.

Best Regards,
Community Support Team _ Zeon Zheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[parry2k]

@Anonymous very hard to understand from the images what is going on. it will be easier if you put sample data in pbix file, share it using one drive/google drive with the expected output.

[Anonymous]

@parry2k 

I can prepare a file to share later.

But basically I got a column that tells me the status of certain project (Open, Suspended, Closed, etc)

I just want to have a line chart that counts the number of projects in each status, for the Last 12 monthes.