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

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