YoY comparison using Dynamic filter through DAX query

[Saibal_78]

Hi All,

I have below mentioned data table, I need to create a comparison between current year spend vs. previous year spend based on the multiple value selected in the dynamic filter.

DAX queries created so far:- 

Measure 1

Total Spend = SUM('Data 1'[Sepnd])

Measure 2

Current Period Spend = 

Var SelectedPeriod = ALLSELECTED(Period[Period])

return

CALCULATE([Total Spend],

FILTER('Data 1','Data 1'[Period] in SelectedPeriod))

Measure 3

Total Spend Previous Year = SUM('Data 2'[Sepnd])          (Data 2 table has similar data which i am using to pull previous year data)

Measure 4

Previous Period Spend =

Var SelectedPeriodPrev = ALLSELECTED(Period[Prev Period])

return

CALCULATE([Total Spend Previous Year],

FILTER('Data 2','Data 2'[Period] in SelectedPeriodPrev))

I have got the current year data in the way I want, however previous year data is not correct. I selected two periods in current year i.e. 202101 (Jan 2021) and 202102 (Feb 2021), hence previous year should take 202001 (Jan 2020) and 202002 (Feb 2020) only but it's taking other periods data for 2020 as well, not sure where is the mistake.

Anyone can help me? Data table and Output is shown in the screen shot.

Thanks

Data1 and Data2Period TableDynamic Filter & Output

[Saibal_78]

Not working, it's showing full year spend

[VahidDM]

@Saibal_78 

Did you select any Period on the Slicer?

Appreciate your Kudos!!

[Saibal_78]

 I see the issue is I have selected Period, I should have selected Date in the slicer instead of Period, Thanks

[Saibal_78]

yes 202101 and 202102

[VahidDM]

Try this:

Previous Period Spend = 
CALCULATE (
    SUM ( 'Table'[Spend] ),
    SAMEPERIODLASTYEAR ( 'Table'[Date] )
)

[VahidDM · Accepted solution]

Hi @Saibal_78 

First, add a column with this code to convert your period to date format:

Date = 
DATE ( LEFT ( 'Table'[Period], 4 ), RIGHT ( 'Table'[Period], 2 ), 01 )

Then use this Measure to find last year amount:

Previous Period Spend = 
CALCULATE (
    SUM ( 'Table'[Spend] ),
    REMOVEFILTERS ( 'Table'[Period] ),
    SAMEPERIODLASTYEAR ( 'Table'[Date] )
)

For Current Period Spend, you don't need to use Measure. Just add the spend column to the card visual.

Output:

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.

Appreciate your Kudos!!