Forum Discussion

Jos13's avatar
Jos13
Helper III
6 years ago
Solved

Previous Month

Hi Team,

I have the following measure to get year month (YYYYMM) number for the value selected in the date filter.

Current Year Month = SELECTEDVALUE('Date'[YM no])
I used the following measure to find previous year month number.
Prev Year Month =
var CYM=[Current Year Month]
return CALCULATE(MAX('Date'[YM no]),ALLSELECTED('Date'),KEEPFILTERS('Date'[YM no]<CYM))
but the value returned by Prev Year Month is blank.
 
Kindly help
 
Data Protection
  • v-lid-msft's avatar
    v-lid-msft
    6 years ago

    Hi Jos13 ,

     

    We can try to use the following measure to meet your requirement:

     

    Prev Year Month =
VAR CYM =
    MIN ( 'Date'[YM no] )
RETURN
    CALCULATE (
        MAX ( 'Date'[YM no] ),
        FILTER (
            ALL ( 'Date' ),
            'Date'[YM no] < CYM
        )
    )

     


    Best regards,

     

4 Replies

  • v-lid-msft's avatar
    v-lid-msft
    Community Support
    6 years ago

    Hi Jos13 ,

     

    We can try to use the following measure to meet your requirement:

     

    Prev Year Month =
VAR CYM =
    MIN ( 'Date'[YM no] )
RETURN
    CALCULATE (
        MAX ( 'Date'[YM no] ),
        FILTER (
            ALL ( 'Date' ),
            'Date'[YM no] < CYM
        )
    )

     


    Best regards,

     

  • amitchandak's avatar
    amitchandak
    Super User
    6 years ago

    With date calendar and rime intelligence, you can get that easily. Make sure you move all your month year calc to the calendar table

    MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))
last MTD (complete) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-1,MONTH))))
last year MTD (complete) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-12,MONTH))))
last year MTD Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-12,MONTH)))

     

    Appreciate your Kudos.

    • amitchandak's avatar
      amitchandak
      Super User
      6 years ago

      Jos13 has the above replies helped. if you need more help make me @

      Appreciate your Kudos.