topic Re: Dynamic Date Key in DAX Commands and Tips

Dynamic Date Key

Anonymous — Tue, 11 Oct 2022 13:07:20 GMT

Good morning all! Is it possible I can create a dynamic date column in a table from this Date - Month column depicted below? Ideally, I would like the most recent date, in this case Sep 2022 to have a value of 1, and the months prior to keep countining upwards. But after every new updated month, I always want the most recent month to be 1.

Re: Dynamic Date Key

NandanHegde — Tue, 11 Oct 2022 14:45:12 GMT

You can leverage the partition/row number concept of SQL :

stackoverflow.com/questions/59622685/dax-expression-for-row-number-partition-by-order-by-equivalent

Re: Dynamic Date Key

v-yinliw-msft — Thu, 13 Oct 2022 09:47:23 GMT

Hi Anonymous ,

You can try this method:

The year and month of the date in the table are calculated first：

The year and month of the current date are then calculated：

The difference between the months is then calculated:

Column:

Number = IF ( 'Sample Table'[Table Data Month] < 'Sample Table'[Current Month], 'Sample Table'[Current Month] - 'Sample Table'[Table Data Month] + ( 'Sample Table'[Current Year] - 'Sample Table'[Table Data Year] ) * 12, ( 'Sample Table'[Current Year] - 'Sample Table'[Table Data Year] ) * 12 - ( 'Sample Table'[Table Data Month] - 'Sample Table'[Current Month] ) )

Then use the year and month of the data in the table as a filter, use the card visual, introduce the number column, and you can view it:

Is this the result you would expect?

Hope this helps you.

Best Regards,

Community Support Team _Yinliw

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.