Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to Solution.
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))
Where Table1 = your table name,
[CreatedDate] = your date field name.
Let me know if I understood you correct.
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
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))
Where Table1 = your table name,
[CreatedDate] = your date field name.
Let me know if I understood you correct.
@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)
Proud to be a Super User!
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?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
115 | |
99 | |
86 | |
70 | |
62 |
User | Count |
---|---|
151 | |
120 | |
103 | |
87 | |
68 |