Filter table on previous year based on applied filters

[Anonymous]

Hi all

So I need to show a list of sold items based on the user's selected year and month. However, I also need to show the sales from the same month in the previous year. This is going to depened on whichever year the user has selected. So if the user selects January and 2021, I need a table showing sales in Jan 2021 but also another table showing sales in Jan 2020.

I can easily show the sold items for the selected year (i.e. Jan 2021 in the above example) but not for the previous year (only Jan 2020).

Any advice on how I can do this?

[amitchandak]

@Anonymous , In case you have date you can use time intelligence

MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))

last year MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-12,MONTH)))
Previous year Month Sales = CALCULATE(SUM(Sales[Sales Amount]),previousmonth(dateadd('Date'[Date],-11,MONTH)))

You can use a separate table with year , month , year month (YYYYMM)

In that new table have a rank

Month Rank = RANKX(all('Date'),'Date'[Year Month],,ASC,Dense)

Then create measure like

This Month = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Month Rank]=max('Date'[Month Rank])))
Last Month = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Month Rank]=max('Date'[Month Rank])-1))

Last Year Month = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Month Rank]=max('Date'[Month Rank])-12))

or

This month = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Year]=max('Date'[Year]) && 'Date'[Month] = Max('Date'[Month]) ))
last year same month = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Year]=max('Date'[Year])-1 && 'Date'[Month] = Max('Date'[Month])))

[Anonymous]

Sorry but I am unsure how this would work. I need a list of sales, with their various details, not just the number of total sales in the required periods. I already have the 2 tables set up showing the sales details I need, I just need to add the appropriate period filter. Can you explain how to use your measures as filters or if they are used in a different way?

[v-easonf-msft · Accepted solution]

Hi, @Anonymous 

Try follow steps:

1.Create a calendar date table as below:

Calculated table:

Calendar = CALENDARAUTO()

Add a calculated column:

Date of Previous year = DATEADD('Calendar'[Date],-1,YEAR)Date of Previous year = DATEADD('Calendar'[Date],-1,YEAR)

2.Create measures as below and drag them to each visual filter pane.

1)the first table visual (current year)

visual control(current year) =
IF (
    SELECTEDVALUE ( 'Fact Table'[Date] ) >= MIN ( 'Calendar'[Date] )
        && SELECTEDVALUE ( 'Fact Table'[Date] ) <= MAX ( 'Calendar'[Date] ),
    1,
    0
)

2. the second table visual(last year)

visual control(last year) =
IF (
    SELECTEDVALUE ( 'Fact Table'[Date] )
        >= MIN ( 'Calendar'[Date of Previous year] )
        && SELECTEDVALUE ( 'Fact Table'[Date] )
            <= MAX ( 'Calendar'[Date of Previous year] ),
    1,
    0
)

 

Result:

Please check my sample pbix for more details.

Best Regards,
Community Support Team _ Eason
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.