Dax LikeForLike previous year

[Franco]

Hi all,

we are facing the following issue:

We have to calculate the total amount of transactions table (month per month) of the previous year only for POS that in the current/selected year have LikeForLike column = TRUE.

The measure TotalTransaction is CALCULATE(SUM(Transactions[Transactions]),Transactions[LikeForLike] = TRUE())

But the TotalTransaction for previous year cannot be calculate simply using CALCULATE(SUM(Transactions[Transactions]),Transactions[LikeForLike] = TRUE(), SAMEPERIODLASTYEAR(Date)) because it would calculate the amount of the last year but for POS that have LikeForLike = TRUE in the previus year.

If i should write it in SQL i would do like the following script:

Select sum(transactions) from transactions 
where year(date) = 2019 and
pos in (
Select distinct pos where year(date) = 2020 and lfl = true
)

 thanks in advance for your reply

Franco

[amitchandak]

@Franco , Not very clear.

Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

you should use date table

CALCULATE(SUM(Transactions[Transactions]),Filter(Transactions,Transactions[LikeForLike] = TRUE()))

last year measure

= CALCULATE(SUM(Transactions[Transactions]),Filter(Transactions,Transactions[LikeForLike] = TRUE()),SAMEPERIODLASTYEAR(Date[Date]))

= CALCULATE(SUM(Transactions[Transactions]),Filter(Transactions,Transactions[LikeForLike] = TRUE()),dateadd(Date[Date],-1,year))

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.

[Franco]

@amitchandak many thanks for the answer.

Below the link to a Pibx with example.

https://www.dropbox.com/s/eixqy5saw5k8k2a/Demo_LikeForLike.pbix?dl=0

Thanks in advance,

Franco