Dynamic Date Key

[Anonymous]

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. 

[v-yinliw-msft · Accepted solution]

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.

[NandanHegde]

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

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