The data is in one table and that table contains al of our tickets with ticketinfo such as client name, start- and enddate.
What i expect is that when i use the timeline visual and select for example only january of 2017 it reads 100 tickets and the average is 100/4 (weeks)= 25 tickets per week
When i selecte Q1 of 2017 it should read 150 tickets but it should also calculate the average number of tickets that periode.
If i select Q1 in my timeline visual it should check the earliest date in my selection and check my last date in my selection and then calculate the number of weeks in between these 2 dates and calculate:
[number of tickets with a startdate in the given period] / [number of weeks in the given period] = average
When i want an average per week i need a column right ? but when i want an average per month, quarter and or year do i need 4 extra colums ?
number of weeks in the given period = CALCULATE ( DATEDIFF ( MIN('Calendar'[DateKey]), MAX ('Calendar'[DateKey] ), WEEK), ALLSELECTED ( 'Calendar'[DateKey] ) )
Then create a measure in your fact table to calculate count of tickets.(In my example, I calculate sum of salesamount, in your scenario, the DAX should be: number of tickets= COUNT(Opdrachtgever[Aantal Tickets]))
Selectedamount = SUM(Sales[SalesAmount])
At last, create a measure using the DAX below.( in your scenario, the DAX should be: Average = [number of tickets]/[number of weeks in the given period])
Average = [Selectedamount]/[number of weeks in the given period]
Thanks, Lydia Zhang
Community Support Team _ Lydia Zhang If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Do you right click your calendar table and choose "New column" to apply DAX of StartDate and EndDate? Check the following DAX. Also what is the Date[Column] in your table? Please remove this column and then create EndDate column.