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

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

 

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

Thank you so much, it works now 😄