Forum Discussion

prasy14's avatar
prasy14
Helper I
8 years ago
Solved

Current Year Previous Month DAX

Hello All,

 

 

I am not getting correct t calculated value for previous Month column , For example in below screen shot CM sales for March =3,881,693. in PM Sales column of April month i am expecting 3,881,693.

CM Sales = CALCULATE(SUM('sales'[salesamt]), bcalendar[byear]= YEAR(TODAY()))-- Current Year Sales by month

PM Sales = CALCULATE (SUM('sales'[salesamt]),PREVIOUSMONTH(bcalendar[bdate]), bcalendar[biyear]= YEAR(TODAY())) ---Current Year Previous Month Sales

 

 

 

 

Need Help
  • v-yulgu-msft's avatar
    v-yulgu-msft
    8 years ago

    Hi prasy14,

     

    Please try this:

    PM Sales =
CALCULATE (
    SUM ( 'Sales'[SalesAmount] ),
    FILTER (
        ALLSELECTED ( 'bcalendar' ),
        'bcalendar'[bdate].[MonthNo]
            = MONTH ( MAX ( 'bcalendar'[bdate] ) ) - 1
            && 'bcalendar'[byear] = YEAR ( TODAY () )
    )
)

    Regards,

    Yuliana Gu

7 Replies

  • mattbrice's avatar
    mattbrice
    Solution Sage
    8 years ago

    You can try:

     

    PM Sales = CALCULATE ( [CM Sales],PREVIOUSMONTH(bcalendar[bdate]) ) ---Current Year Previous Month Sales

    Though if it were me, i'd have a 'Year' slicer, to make it easier.

     

     

    • prasy14's avatar
      prasy14
      Helper I
      8 years ago

      Hello mattbrice,

       

      i have to display Current Year,Last Year, LLY,LLLY by months as well that is reason i am not using year slicer.

      the code which you have provided is giving me blank values.

      • mattbrice's avatar
        mattbrice
        Solution Sage
        8 years ago

        Does your 'bcalendar' table include dates for the prior month? prior year? 

  • v-yulgu-msft's avatar
    v-yulgu-msft
    Microsoft Employee
    8 years ago

    Hi prasy14,

     

    Please try this:

    PM Sales =
CALCULATE (
    SUM ( 'Sales'[SalesAmount] ),
    FILTER (
        ALLSELECTED ( 'bcalendar' ),
        'bcalendar'[bdate].[MonthNo]
            = MONTH ( MAX ( 'bcalendar'[bdate] ) ) - 1
            && 'bcalendar'[byear] = YEAR ( TODAY () )
    )
)

    Regards,

    Yuliana Gu