Forum Discussion
How to Assign Month Number Dynamically
Assign Month Number Dynamically
Example =
feb=1
mar=2
april=3
.
.
.
dec=11
current month jan is =12
Last 12 month data show
Moving AvG Prob
cureent month is jan =12
veer,
Please create calculated column in your table with formula below:
DynamicMonth = IF(MOD(MONTH(Table1[CreatedDate]) - MONTH(TODAY())+12,12)=0,12,MOD(MONTH(Table1[CreatedDate]) - MONTH(TODAY())+12,12))
Dynamic month in DAX
Where Table1 = your table name,
[CreatedDate] = your date field name.
Let me know if I understood you correct.
7 Replies
veer best thing to do is have a calendar table where you create a column called month number where you can assign with an if statement, alternatively you can use switch statement to create a calculated column, or you could also create a if statement in power query (m)
month no = switch(month,
"feb", 1,
"mar",2 etc)
I'm sorry if I'm missing the point, but can't you just use a relative date filter to always give you the last 12 months?
vanessafvg I want dynamic month number .
veer,
What do you mean under dynamically here?
I see that you just made offset. Do you mean each time Feb = 1, .., Apr = 3 etc.? And it would be for now, January and the same would be for next months like every time Feb = 1, .., Apr = 3 etc?
Or in February you should have Mar = 1,..,May=3, etc?
Where do you plant to user this month number? Or just to sort chart you've shown?
zoloturucurrent month is -Jan ..so i want current month number is 12
current, jan 12, _current - 1, 11, _current - 2, 10, _current - 3, 9, _current - 4, 8, _current - 5, 7, _current - 6, 6, _current - 7, 5, _current - 8, 4, _current - 9, 3, _current - 10, 2, _current - 11, 1,this type logic i want ..in dax
veer,
Please create calculated column in your table with formula below:
DynamicMonth = IF(MOD(MONTH(Table1[CreatedDate]) - MONTH(TODAY())+12,12)=0,12,MOD(MONTH(Table1[CreatedDate]) - MONTH(TODAY())+12,12))
Dynamic month in DAX
Where Table1 = your table name,
[CreatedDate] = your date field name.
Let me know if I understood you correct.
