topic Re: How to create a monthly frequency column from latest updated date column? in DAX Commands and Tips

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

Anonymous — Mon, 15 Aug 2022 22:06:41 GMT

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.

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

amitchandak — Tue, 16 Aug 2022 02:35:38 GMT

Anonymous , 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-data-to-it-using-incremental-refresh/
https://www.thebiccountant.com/2017/01/11/incremental-load-in-powerbi-using-dax-union/

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

Anonymous — Fri, 19 Aug 2022 07:10:46 GMT

Hi Anonymous ,

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.