Forum Discussion

Franco's avatar
Franco
Frequent Visitor
5 years ago

Dax LikeForLike previous year

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

2 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    5 years ago

    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.