How to create a monthly frequency column from latest updated date column?

[ajinkya777]

We have datasheet with columns user-id and date when the presentation was last updated. I want to create new columns that register a monthly frequency score whenever the date column gets updated. For example, if date gets updated in month of January twice the frequency score will be two for January and if date gets updated thrice in month of February the frequency score will be three and so on and so forth.

[v-binbinyu-msft · Accepted solution]

Hi @ajinkya777 ,

Please try below steps

1. my test table

2. add two new column

Jan Freq =
VAR cur_id = 'Table'[User-id]
VAR tmp =
    FILTER (
        'Table',
        'Table'[User-id] = cur_id
            && MONTH ( 'Table'[Latest update date] ) = 1
    )
RETURN
    COUNTROWS ( tmp )

Feb Freq =
VAR cur_id = 'Table'[User-id]
VAR tmp =
    FILTER (
        'Table',
        'Table'[User-id] = cur_id
            && MONTH ( 'Table'[Latest update date] ) = 2
    )
RETURN
    COUNTROWS ( tmp )

Please refer attached .pbix file

Best regards,
Community Support Team_ Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@ajinkya777 , is it snapshot type of column , or you want to create snapshot on power bi track changes

If that is snapshot you can use distinctcount

if not you can create one using

https://blog.crossjoin.co.uk/2020/04/13/keep-the-existing-data-in-your-power-bi-dataset-and-add-new-...
https://www.thebiccountant.com/2017/01/11/incremental-load-in-powerbi-using-dax-union/