Forum Discussion

Arkhos94's avatar
Arkhos94
Helper IV
5 years ago

Compare data from 2 years

I have two database :

one with a list of trainee and the ID of the training they followed

one other with the list of all training and its date (with one column being the category of training : sales, technical, other)

 

=> I want to count how many trainee i have => I use counta to count the name of the trainee (NbTrainee = counta('traineeDB"[TraineeID])

 

=> I want to check the number of trainee per year => I do a matrix with training category as line, number of traines as value, and year as column and end up with something like this 

TrainingTopic20192020
Sales100110
Technical200250
Other108

 

Next step (and here I have a problem), I want to add a fourth column that compare 2019 and 2020, like this :

TrainingTopic20192020Dif.
Sales100110+10%
Technical200250+25%
Other108-20%

 

I was advised to do it using two calculated measurment :

NbTrainee2019 = calculate(counta('traineeDB'[TraineeID],'Year'="2019")

NbTrainee2020 = calculate(counta('traineeDB'[TraineeID],'Year'="2020")

 

It should work, except each time I want to compare two other years (eg. 2019vs2018 or 2020vs2019) I will have to change the measurments.

 

Is there a way to write a measurment like this ? With the user being able to choose the "chosen year"

NbTrainee201X = calculate(counta('traineeDB'[TraineeID],'Year'=Chosen year)

NbTrainee201Y = calculate(counta('traineeDB'[TraineeID],'Year'=Chosen year-1)

 

Can someone help me ?

 

Need Help

1 Reply

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    Arkhos94 , Can try with Date table and time intelligence

     

    YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"12/31"))
    Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"))
    This year Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR('Date'[Date]),"12/31"))
    Last year Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR(dateadd('Date'[Date],-1,Year)),"12/31"))
    Last to last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-2,Year),"12/31"))
    Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))

     

    //Only year vs Year, not a level below. Below can work with the Year table, no need of date


    This Year = CALCULATE(sum('order'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])))
    Last Year = CALCULATE(sum('order'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])-1))

     

    diff = [This Year]-[Last Year ]
    diff % = divide([This Year]-[Last Year ],[Last Year ])

     

    Power BI — Year on Year with or Without Time Intelligence
    https://medium.com/@amitchandak.1978/power-bi-ytd-questions-time-intelligence-1-5-e3174b39f38a

     

    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.