Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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]
)))
Solved! Go to Solution.
Hi,
Here is my test table:
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:
Here is my test pbix file:
Hope this helps.
Best Regards,
Giotto Zhi
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/
Hi,
Here is my test table:
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:
Here is my test pbix file:
Hope this helps.
Best Regards,
Giotto Zhi
Hi,
Try this approach
Count = DISTINCTCOUNT(PayrollTable[Employee])
Count last year = CALCULATE([Count],SAMEPERIODLASTYEAR(Calendar[Date]))
Hope this helps.
User | Count |
---|---|
139 | |
113 | |
103 | |
73 | |
63 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |