hardcode last month date

[Anonymous]

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

[v-deddai1-msft · Accepted solution]

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

[v-deddai1-msft · Accepted solution]

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

[Anonymous]

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 )

[v-deddai1-msft · Accepted solution]

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]

thanks ALOT!!!!! you really saved me hours of headache!!!!! i wish you a very very good day 🙂 

[v-deddai1-msft]

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

[amitchandak]

@Anonymous ,

Calculate ([measure],eomonth(Table[Date],0)=eomonth(Today(),-1)) 

or

Calculate ([measure],filter(all(Date), eomonth(Date[Date],0)=eomonth(Today(),-1)) )

[Anonymous]

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. 

[amitchandak]

@Anonymous , if should give only may, till next may 

measure =
var _1 = if(month(Today())>5, month(Today()) -5 , month(Today()) +7)
return
Calculate ([measure],eomonth(Table[Date],0)=eomonth(Today(),-1*_1))

[v-deddai1-msft]

Hi @Anonymous ,

Did you try my measure?

Best Regards,

Dedmon Dai

[Anonymous]

Hi dedmon, 

thanks alot for your suggestion but your measure results in a "moving month" as well. I need the month to be static for the rest of the year. meaning if its july now then we should filter june  for the rest of the year. but when we move into aug, we should filter july data and use that for the rest of the year. like a drag on formula. 

[Anonymous]

[Ashish_Mathur]

Hi,

Refer to this article - LASTNONBLANK Explained