Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

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 l...
Need Help
  • v-deddai1-msft's avatar
    v-deddai1-msft
    5 years ago

    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's avatar
    v-deddai1-msft
    5 years ago

    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's avatar
Anonymous
Not applicable
5 years ago

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's avatar
v-deddai1-msft
Community Support
5 years ago

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.