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
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.
Solved! Go to Solution.
Hi @PrivateAnalytic ,
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.
Hi @PrivateAnalytic ,
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.
You can leverage the partition/row number concept of SQL :
stackoverflow.com/questions/59622685/dax-expression-for-row-number-partition-by-order-by-equivalent
User | Count |
---|---|
37 | |
36 | |
30 | |
29 | |
21 |
User | Count |
---|---|
61 | |
41 | |
32 | |
18 | |
16 |