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

YTD and 12m Roling Average on Distinct Values by year and Month

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

2 ACCEPTED SOLUTIONS
v-eachen-msft
Community Support
Community Support

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] ) )
)

3-1.PNG
Here is my test file for your reference.

 

Community Support Team _ Eads
If this post helps, then please consider Accept it as the solution to help the other members find it.

View solution in original post

Anonymous
Not applicable

# 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

View solution in original post

5 REPLIES 5
v-eachen-msft
Community Support
Community Support

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] ) )
)

3-1.PNG
Here is my test file for your reference.

 

Community Support Team _ Eads
If this post helps, then please consider Accept it as the solution to help the other members find it.
Anonymous
Not applicable

# 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

Anonymous
Not applicable

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

amitchandak
Super User
Super User

//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

Anonymous
Not applicable

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 🙂

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.