Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
PrivateAnalytic
Helper IV
Helper IV

Dynamic Date Key

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. 

PrivateAnalytic_0-1665493574710.png

 

1 ACCEPTED SOLUTION
v-yinliw-msft
Community Support
Community Support

Hi @PrivateAnalytic ,

 

You can try this method:

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

vyinliwmsft_0-1665654029982.png

vyinliwmsft_1-1665654053859.png

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

vyinliwmsft_2-1665654091477.png

vyinliwmsft_4-1665654125724.png

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] )
)

 

vyinliwmsft_5-1665654245888.png

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:

vyinliwmsft_6-1665654339544.png

vyinliwmsft_8-1665654372249.png

 

 

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.

View solution in original post

2 REPLIES 2
v-yinliw-msft
Community Support
Community Support

Hi @PrivateAnalytic ,

 

You can try this method:

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

vyinliwmsft_0-1665654029982.png

vyinliwmsft_1-1665654053859.png

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

vyinliwmsft_2-1665654091477.png

vyinliwmsft_4-1665654125724.png

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] )
)

 

vyinliwmsft_5-1665654245888.png

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:

vyinliwmsft_6-1665654339544.png

vyinliwmsft_8-1665654372249.png

 

 

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
Solution Sage
Solution Sage

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

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

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

Top Solution Authors