Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Last year same month with slicer

I'm trying to get my sales for the same month last year but specific to the slicer. When I use this formula:     Last Year Same Month = CALCULATE ( SUM ('Sell In'[Sales]), FILTER ( ...
Need Help
Anonymous's avatar
Anonymous
Not applicable
7 years ago

Is there no way to create a column that calculates the previous year's same month column? So I can create a column where it shows the previous year's month but then want something like this:

 

 

And the do a sumifs formula to get the sales for each product:

 

 

 

 

 

Anonymous's avatar
Anonymous
Not applicable
7 years ago

HI Anonymous ,

You can try to use following calculated column formula if it works for your requirement:

Last Year Same Month =
CALCULATE (
    SUM ( 'Table'[Sales] ),
    FILTER (
        ALL ( 'Table' ),
        YEAR ( 'Table'[Month] )
            = YEAR ( EARLIER ( 'Table'[Month] ) ) - 1
            && MONTH ( 'Table'[Month] ) = MONTH ( EARLIER ( 'Table'[Date] ) )
            && Table[Product] = EARLIER ( Table[Product] )
    )
)

If above not help, please share a pbix file with some sample data for test.

Regards,
Xiaoxin Sheng

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Hi,

     

    When I applied the formula, it gave me a figure much higher than anticipated. I realised I also needed to include retailer. I tried to do that:

     

    Last Year Same Month =
CALCULATE (
    SUM ( 'Table'[Sales] ),
    FILTER (
        ALL ( 'Table' ),
        YEAR ( 'Table'[Month] )
            = YEAR ( EARLIER ( 'Table'[Month] ) ) - 1
            && MONTH ( 'Table'[Month] ) = MONTH ( EARLIER ( 'Table'[Date] ) )
            && Table[Product] = EARLIER ( Table[Product] )
            && Table[Retailer] = EARLIER ( Table[Retailer] )
    )
)

    it gives me the following error:

    "Cannot nest EARLIER/EARLIEST functions."

     

    Thanks

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      HI Anonymous ,

      If you can please share us a sample pbix file for test, it will help for test to modify dax formula.

      Regards,

      Xiaoxin Sheng