Forum Discussion
hardcode last month date
Hi All,
I currently i have this measure and i would like it to always filter last month. How would i do that?
Calculate (measure, Date(yearmonth)=202105)
Since this month is june i would like it to show MAY(like in the example above) and in future always to show the previous month (like in the example above).
Hi Anonymous ,
My formula is based on Today(), I don't think it will result in moving month in the forward months. Did you have a Date table in your date model?
My understanding for your case is when today is 2021/7/1 Then all the months forward will show value for 2021 June. If today is 2021/6/30 . All the months forward will show value for 2021 May.
A little modification of my formula :
Measure = VAR A = IF ( RIGHT ( FORMAT ( TODAY (), "YYYYMM" ), 2 ) = "01", VALUE ( FORMAT ( TODAY (), "YYYYMM" ) ) - 100 + 11, VALUE ( FORMAT ( TODAY (), "YYYYMM" ) - 1 ) ) RETURN CALCULATE ( [MEASURE], FILTER(ALL(Date),Date[yearmonth] = A ))If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Best Regards,
Dedmon Dai
Hi Anonymous ,
For example, today is 2020/1/1, so its yearmonth equals to 202001, if you need to get the previous month it is 2019 Dec, its yearmonth column should be 201912, so I use -100+11 in the formula. Similarly, if today is 2019/1/1 the yearmonth for previous month is 201812 = 201901-100+11.
If today is 2020/2/1, its yearmonth equals to 202002, the previous month 's year month 202001 =202002-1. The condition in if formula is used to distinguish the first month of a year and other months.
Best Regards,
Dedmon Dai.
12 Replies
Hi Anonymous ,
My formula is based on Today(), I don't think it will result in moving month in the forward months. Did you have a Date table in your date model?
My understanding for your case is when today is 2021/7/1 Then all the months forward will show value for 2021 June. If today is 2021/6/30 . All the months forward will show value for 2021 May.
A little modification of my formula :
Measure = VAR A = IF ( RIGHT ( FORMAT ( TODAY (), "YYYYMM" ), 2 ) = "01", VALUE ( FORMAT ( TODAY (), "YYYYMM" ) ) - 100 + 11, VALUE ( FORMAT ( TODAY (), "YYYYMM" ) - 1 ) ) RETURN CALCULATE ( [MEASURE], FILTER(ALL(Date),Date[yearmonth] = A ))If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Best Regards,
Dedmon Dai
Hi Dedmon,
Would you be so kind as to explain this part of the formula a little? 😁 Everything works!!! i just need to explain it to my stakeholders how this is done and im abit confused
RIGHT ( FORMAT ( TODAY (), "YYYYMM" ), 2 ) = "01", VALUE ( FORMAT ( TODAY (), "YYYYMM" ) ) - 100 + 11, VALUE ( FORMAT ( TODAY (), "YYYYMM" ) - 1 )Hi Anonymous ,
For example, today is 2020/1/1, so its yearmonth equals to 202001, if you need to get the previous month it is 2019 Dec, its yearmonth column should be 201912, so I use -100+11 in the formula. Similarly, if today is 2019/1/1 the yearmonth for previous month is 201812 = 201901-100+11.
If today is 2020/2/1, its yearmonth equals to 202002, the previous month 's year month 202001 =202002-1. The condition in if formula is used to distinguish the first month of a year and other months.
Best Regards,
Dedmon Dai.
Anonymous ,
Calculate ([measure],eomonth(Table[Date],0)=eomonth(Today(),-1))
or
Calculate ([measure],filter(all(Date), eomonth(Date[Date],0)=eomonth(Today(),-1)) )
HI,
thanks alot for the quick reply!!
I have tried your measure but for the future months, it still calculates a moving "previous month".
What I would like is that in Jun, jul , aug ,sept.... it should only filter by MAY. i.e all the months going forward I would like to show a static MAY. The background is that I am calculating an allocation key based on trends for the past 6 months. And I would like to use a static allocation key (MAY) for all the months going forward for the rest of the year.
Hi Anonymous ,
Did you try my measure?
Best Regards,
Dedmon Dai
Hi Anonymous ,
You can use the following measure
Measure = VAR A = IF ( RIGHT ( FORMAT ( TODAY (), "YYYYMM" ), 2 ) = "01", VALUE ( FORMAT ( TODAY (), "YYYYMM" ) ) - 100 + 11, VALUE ( FORMAT ( TODAY (), "YYYYMM" ) - 1 ) ) RETURN CALCULATE ( [MEASURE], Date[yearmonth] = A )If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Best Regards,
Dedmon Dai
