Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi Power BI experts!
This is my first post, so please let me know if more information is needed! I have a question on how to solve the following problem:
I would like to count the Year to Date unique values (employees/customers) by year and month. I also would like to have a roling average on the distinct values. Is it necessary to create a new table or can it be in one measure?
Can anyone help me with this?
I add my pbix file in this post to make it easier what I try to achieve.
Thank in advantage for any help
Regard, Rik
Solved! Go to Solution.
Hi @Anonymous ,
You could create a measure to get the result.
Measure 2 =
SUMX (
FILTER (
ALL ( Transactions[yyyymm] ),
'Transactions'[yyyymm] <= MAX ( Transactions[yyyymm] )
),
CALCULATE ( DISTINCTCOUNT ( Transactions[Employee] ) )
)
Here is my test file for your reference.
# Clienten PE YTD = SUMX ( FILTER ( ALL ( calender ) ; calender[year] = MAX ( calender[year] ) && calender[Date] <= MAX(calender[Date]) ); CALCULATE ( DISTINCTCOUNT ( 'Testing'[ClientId] ) ) )
I found this as the sollution for the ytd calculation on values based on the sollution you gave me. Many Thanks
Hi @Anonymous ,
You could create a measure to get the result.
Measure 2 =
SUMX (
FILTER (
ALL ( Transactions[yyyymm] ),
'Transactions'[yyyymm] <= MAX ( Transactions[yyyymm] )
),
CALCULATE ( DISTINCTCOUNT ( Transactions[Employee] ) )
)
Here is my test file for your reference.
# Clienten PE YTD = SUMX ( FILTER ( ALL ( calender ) ; calender[year] = MAX ( calender[year] ) && calender[Date] <= MAX(calender[Date]) ); CALCULATE ( DISTINCTCOUNT ( 'Testing'[ClientId] ) ) )
I found this as the sollution for the ytd calculation on values based on the sollution you gave me. Many Thanks
Thanks for the sollution on YTD count values
Is this also possible by not creating an extra column in the transaction table? By using the calendar table for example. By combining the YTD measure to Divide ( YTD hours , YTD Unique Employees) by year and month it won't work against the calendar table.
Any suggestions on this?
Regards, Rik
//Did not check the pbix yet
Rolling Count of distinct try values.
For YTD you can use datesytd or totalytd. Make sure you have a calendar table defined
Rolling 12 distinct =
var _max = max(Sales[Sales Amount])
return
CALCULATE(AVERAGEX(values(Sales[Sales Amount]),_max),DATESINPERIOD('Date'[Date Filer],ENDOFMONTH(Sales[Sales Date]),-12,MONTH))
ytd = CALCULATE(DISTINCTCOUNT(Sales[Sales Amount]),DATESYTD(ENDOYEAR('Date'[Date])))
calendar
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks.
My Recent Blog - https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601
Thanks for your reply. I tested it on values instead of amount. It didn't work. I have several calculations on numbers and with that there are no problems. Distinct values is my problem 🙂
User | Count |
---|---|
141 | |
113 | |
104 | |
78 | |
64 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |