Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Reyesnes
Helper I
Helper I

Calculate the LY y YoY% of headcount

Hello friends, I am working on an HR Analytics dashboard, in which I want to calculate the following with DAX formulas:

 

1. Calculate Last Year

2. Calculate the YoY%

 

As data to take into account:

  • In the dashboard I will make filters at the year level
  • I already have a headcount DAX :
Headcount= SUM('01_Master Headcount'[Headcount])
  • and the graph that I want to create is exactly this:

Reyesnes_1-1623771409883.png

 

The data is assembled as follows (you can download the complete excel file with the data from this link: here)

 

Reyesnes_0-1623771302735.png

 

I hope you can help me. I'll be very greatful,

 

Nestor Reyes

15 REPLIES 15
ERD
Super User
Super User

Hi @Reyesnes ,

You might try these measures (I assume you have a proper Calendar table connected to your Data table by Date column):

HeadcountSum = SUM(T[Headcount])
HeadcountAmt = 
VAR lastAvailableYearMonOrder =
    CALCULATE (
        LASTNONBLANK ( 'Calendar'[YearMonOrder],  [HeadcountSum] ),
        ALL ( 'Calendar'[Month num] )
    )
VAR mostRecentAmt =
    CALCULATE (
        [HeadcountSum],
        'Calendar'[YearMonOrder] = lastAvailableYearMonOrder,
        ALL ( 'Calendar'[Month num] )
    )
VAR result = COALESCE ( [HeadcountSum], mostRecentAmt )
RETURN
    result
LY = CALCULATE ( [HeadcountAmt], SAMEPERIODLASTYEAR ( 'Calendar'[Date] ) )
YOY % =
VAR CY = [HeadcountAmt]
VAR LY = [LY]
VAR YOY = IF ( NOT ISBLANK ( CY ) && NOT ISBLANK ( LY ), CY - LY )
RETURN
    YOY / LY

ERD_1-1623850426921.png

If this post helps, then please consider Accept it as the solution ✔️to help the other members find it more quickly.

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Appreciate your Kudos.

Check out my latest demo report in the data story gallery.

Stand with Ukraine!


Here are official ways you can support Ukraine financially (accounts with multiple currencies):
1) Support the Armed Forces of Ukraine: https://bank.gov.ua/ua/about/support-the-armed-forces
2) Come Back Alive foundation: https://www.comebackalive.in.ua/

Thank you!

@ERD Thank you.

Can you share the .pbix with me, please?

@Reyesnes ,

Can't upload it, sorry. Just create a new .pbix with your sample data (copy/past it in the Power Query Editor) and then copy/past measures.

P.S.: I've used a very simple Calendar table just to play with:

Calendar = 
VAR minY = 2020
VAR maxY = 2022
RETURN
ADDCOLUMNS(
    FILTER(CALENDARAUTO(), YEAR([Date]) <= maxY && YEAR([Date]) >= minY),
    "Month", FORMAT([Date], "mmmm"),
    "Month num", MONTH([Date]),
    "weekNum", WEEKNUM([Date]),
    "Year", YEAR([Date]),
    "YearMonOrder", YEAR([Date]) *100 + MONTH([Date])
)

If this post helps, then please consider Accept it as the solution ✔️to help the other members find it more quickly.

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Appreciate your Kudos.

Check out my latest demo report in the data story gallery.

Stand with Ukraine!


Here are official ways you can support Ukraine financially (accounts with multiple currencies):
1) Support the Armed Forces of Ukraine: https://bank.gov.ua/ua/about/support-the-armed-forces
2) Come Back Alive foundation: https://www.comebackalive.in.ua/

Thank you!

Hello, indeed, in Data Model I already have a calendar table.

Question: The column that you show as "YearMonOrder", what does it do is the same as what I have as "PeriodNumber"?
Like this:

 

Calendar_Table.png

 

It just shows the year and a month. Created just to have it as in your example. The connection is via Date column anyway.

ERD_0-1623866163628.png

If this post helps, then please consider Accept it as the solution ✔️to help the other members find it more quickly.

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Appreciate your Kudos.

Check out my latest demo report in the data story gallery.

Stand with Ukraine!


Here are official ways you can support Ukraine financially (accounts with multiple currencies):
1) Support the Armed Forces of Ukraine: https://bank.gov.ua/ua/about/support-the-armed-forces
2) Come Back Alive foundation: https://www.comebackalive.in.ua/

Thank you!

@ERD  I've been liking your solution. What happens perhaps is that the table I provided for this, was just an excerpt from to exemplify. However, my data model is still superior to that and I don't know where I can be failing.

 

However, I have been applying what you have been indicating to me, and the result that it leads me to is this (I have not been successful):

 

01_Results.png

 

 

 

 

My actual data model has the following structure:

02_DataModel 1.png

I have the tables related to this column called "DateId", it is a calculated column that is found in all my tables. It is simply the column "Date" converted to Text format ".
For example if I have this date in the "Date" column: dd / MM / YYYY, what this calculated column "DateId" does is the same as "dd | MM | YYYY".  Like this:

 

03_ DateId Relation.png

 

Then as for the headcount DAX, from there we are both fine

(Unlike the DAX I have it named as "_Headcount Fin Mes").

 

_Headcount Fin Mes = SUM('01_Master Headcount'[Headcount])

I have applied the DAX functions you sent me, obviously applying it according to my table structure or model:

HeadcountAmt =
VAR lastAvailableYearMonOrder =
CALCULATE (
LASTNONBLANK ( Date_matrix[PeriodNumber], [_Headcount Fin Mes] ),
ALL ( Date_matrix[MonthNumber] )
)
VAR mostRecentAmt =
CALCULATE (
[_Headcount Fin Mes],
Date_matrix[PeriodNumber] = lastAvailableYearMonOrder,
ALL ( Date_matrix[MonthNumber] )
)
VAR result = COALESCE ( [_Headcount Fin Mes], mostRecentAmt )
RETURN
result
HeadcountLY = CALCULATE( [HeadcountAmt],SAMEPERIODLASTYEAR(Date_matrix[Date]))
Headcount YoY % = 
VAR CY = [HeadcountAmt]
VAR LY = [HeadcountLY]
VAR YOY = IF ( NOT ISBLANK (CY) && NOT ISBLANK (LY), CY - LY)
RETURN
YOY / LY


Another thing and no less important to keep in mind, do not lose sight of this graph which I want to reach as a final result:

Reyesnes_0-1623870089036.png

 

The LY comparison has to be monthly. You must compare current month vs same month last year, example:

 

- January 2021: 550
- January 2020: 350 (this would be my HeadcountLY if I have a 2021 filter).

- February 2021: 450
- February 2020: 500 (this would be my HeadcountLY if I have a 2021 filter).

and so on.


Keeping all this in mind and considering that I have explained and given you visibility of my data model, can you identify where I am failing and why I am not reaching the result I want? I will be eternally grateful to you. 

 

Let me know please.

1) In your visual Year, Month, etc. should be from the Calendar table. Mark your Calendar table as Date table.
2) LY: https://dax.guide/sameperiodlastyear/
3) I don't know the particularities of your model and real data, it's hard to say more without seeing it. 

Use line and clustered column chart for the visual. HeadcountAmt and LY as column values and YOY% as Line values.

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Appreciate your Kudos.

Check out my latest demo report in the data story gallery.

Stand with Ukraine!


Here are official ways you can support Ukraine financially (accounts with multiple currencies):
1) Support the Armed Forces of Ukraine: https://bank.gov.ua/ua/about/support-the-armed-forces
2) Come Back Alive foundation: https://www.comebackalive.in.ua/

Thank you!

CNENFRNL
Community Champion
Community Champion

Screenshot 2021-06-16 010149.png


Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension!

DAX is simple, but NOT EASY!

Anonymous
Not applicable

@CNENFRNL 

 

I'd like to point out that time-intel functions do not work correctly with any date column. Only with a proper date table. So, even if today your formula seems to work correctly IT'S NOT GUARANTEED to do so in the future.

Hi daxer, for that I have a calculated calendar table, which is related to this other table of Master Headcount.Relation between Calendar y Master Headcount.png

 

Anonymous
Not applicable

@Reyesnes 

 

That's fine. I was just trying to point out that the formula given by @CNENFRNL may calculate wrong numbers before you know it.

Okay Daxer, I am very clear on this. And I thank you both very much. Every day something new is learned, also for me it has been a lesson learned.

 

Even the tables are not related by the "date" columns with date format.
For each date column in each table (including the calendar table), there is a calculated date column in text format, and the tables are related between these calculated text columns, which I have named: "DateId".

 

In each table, the calculated column looks something like this (example in calendar table):


DateId = VALUE(Date_matrix[Day])&"|"&VALUE(Date_matrix[MonthNumber])&"|"&VALUE(Date_matrix[Year])
In your practice and / or experience, would this be valid?

@Anonymous 

 

Sounds ridiculous to me as you use your understanding of time-intel functions to judge my grasp of it ...

A neat time table is, for sure, the best practice; yet it's meanwhile not the only way. 


Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension!

DAX is simple, but NOT EASY!

Anonymous
Not applicable

@CNENFRNL 

 

Maybe it's ridiculous to you, nevertheless it's true. If you don't believe me, try to ask Alberto Ferrari or Marco Russo. They certainly have a better grasp on DAX than you do. I can only add to it that from my own experience I tell you that if you don't abide by the rule, you'll be in trouble sooner than you think but you will most likely not be even able to tell that your figures are wrong, especially when the model is big.

 

From dax.guide:

 

daxer_0-1623844289085.png

 

amitchandak
Super User
Super User

@Reyesnes , using date table and time intelligence

 

Year behind Sales = CALCULATE([Headcount],dateadd('Date'[Date],-1,Year))
Year behind Sales = CALCULATE([Headcount],SAMEPERIODLASTYEAR('Date'[Date]))

 

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors