Forum Discussion

Nerdywantocode's avatar
Nerdywantocode
Icon for Helper I rankHelper I
2 years ago
Solved

Calculate previous month sales

Hi guys, I'm currently want to calculate previous month sale. This is my data table   For some reasons, I dont want to convert the column month into date to use DAX like PREVIOUS or EAR...
Need Help
Show and Tell
  • Anonymous's avatar
    Anonymous
    2 years ago

    Didn't think about that. Can you not just convert the month to date with DAX in a new column like:


    MonthToDateConverted =     DATE(LEFT(SalesTable[Month], 4), RIGHT(SalesTable[Month], 2), 1)

    Then use 
    PreviousMonthSales =
     CALCULATE(SUM(SalesTable[SALES]), PREVIOUSMONTH(SalesTable[MonthToDateConverted]), REMOVEFILTERS())

     

  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi there,

     

    I think it's because of the REMOVEFILTERS() piece. Could you try changing the measure to use the ALLEXCEPT function;

     

    CALCULATE(
    SUM(SalesTable[SALES]),
    PREVIOUSMONTH(SalesTable[MonthToDateConverted]),
    ALLEXCEPT(SalesTable, SalesTable[Product])
    )

     

     

Anonymous's avatar
Anonymous
Not applicable
2 years ago

If you don't want to convert to date format... It looks like you're using a numerical value, which should always increase in value, so you can just take the max -1 from that value.

 

Sales PrevMo =
VAR PrevMonth = MAX('Table'[Month]) - 1
RETURN
CALCULATE(SUM('Table'[Sales]), 'Table'[Month] = PrevMonth)

Nerdywantocode's avatar
Nerdywantocode
Icon for Helper I rankHelper I
2 years ago

Nice idea, I tried your solution earlier, but the problem when year change, the calculation skip it. The result is like this  😄