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
Anonymous
Not applicable

Count Employees from the last year to create the variance line.

Hello guys!

I appreciate your help with this DAX that is not working for me.  Im creating a chart where I want create the variance from the last 

CountLastYear =
CALCULATE(
DISTINCTCOUNT(PayrollTable[Employee]),
(SAMEPERIODLASTYEAR (PayrollTable[TermDate].[Year]
)))

1 ACCEPTED SOLUTION
v-gizhi-msft
Community Support
Community Support

Hi,

 

Here is my test table:

36.PNG

Please try this column first:

Year = 'PayrollTable'[TermDate].[Year]

Then try this measure:

CountLastYear = 
CALCULATE(
DISTINCTCOUNT(PayrollTable[Employee]),
FILTER(ALLSELECTED(PayrollTable),PayrollTable[Year]=MAX(PayrollTable[Year])-1))

The result shows the distinct count of employee in last year:

35.PNG

Here is my test pbix file:

pbix 

Hope this helps.

 

Best Regards,

Giotto Zhi

View solution in original post

4 REPLIES 4
amitchandak
Super User
Super User

Prefer all-time intelligence calculation with date table

CountLastYear =
CALCULATE(
DISTINCTCOUNT(PayrollTable[Employee]),
(SAMEPERIODLASTYEAR (PayrollTable[TermDate]
)))

Or
CountLastYear =
CALCULATE(
DISTINCTCOUNT(PayrollTable[Employee]),
(SAMEPERIODLASTYEAR ('Date'[Date]
)))

 

You can also try

YTD  = CALCULATE(DISTINCTCOUNT(PayrollTable[Employee]),DATESYTD(('Date'[Date]),"12/31"))
This Year = CALCULATE(DISTINCTCOUNT(PayrollTable[Employee]),DATESYTD((ENDOFYEAR('Date'[Date])),"12/31"))

Last YTD  = CALCULATE(DISTINCTCOUNT(PayrollTable[Employee]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"))
Last YTD complete  = CALCULATE(DISTINCTCOUNT(PayrollTable[Employee]),DATESYTD(ENDOFYEAR(dateadd('Date'[Date],-1,Year)),"12/31"))
Last to last YTD  = CALCULATE(DISTINCTCOUNT(PayrollTable[Employee]),DATESYTD(dateadd('Date'[Date],-2,Year),"12/31"))

Year behind  = CALCULATE(DISTINCTCOUNT(PayrollTable[Employee]),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 :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

v-gizhi-msft
Community Support
Community Support

Hi,

 

Here is my test table:

36.PNG

Please try this column first:

Year = 'PayrollTable'[TermDate].[Year]

Then try this measure:

CountLastYear = 
CALCULATE(
DISTINCTCOUNT(PayrollTable[Employee]),
FILTER(ALLSELECTED(PayrollTable),PayrollTable[Year]=MAX(PayrollTable[Year])-1))

The result shows the distinct count of employee in last year:

35.PNG

Here is my test pbix file:

pbix 

Hope this helps.

 

Best Regards,

Giotto Zhi

Anonymous
Not applicable

Thank you so much! 🙂
Ashish_Mathur
Super User
Super User

Hi,

Try this approach

  1. Create a Calendar Table and build a relationship from the TermDate column of the PayrollTable table to the Date column of the Calendar Table
  2. In the Calenar Table, write a calculated column formula to extract the year: Year = year(Calendar[Date])
  3. To your visual, drag Year from the Calendar Table
  4. Write these measures

Count = DISTINCTCOUNT(PayrollTable[Employee])

Count last year = CALCULATE([Count],SAMEPERIODLASTYEAR(Calendar[Date]))

Hope this helps.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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.