Average count for each month based on the previous three years (DAX)

[lazarus1907]

Hi,

I have a table with events IDs and the dates when they occurred, similar to the one below, but with tens of thousands of rows.

Events table
Event ID	Date
147833	01-Jun-20
147834	01-Jun-20
147835	02-Jun-20
147836	02-Jun-20
147837	02-Jun-20
147838	03-Jun-20

Most simple measures I used are pretty straight forward, e.g.

Count = count('Events table'[Date])

Cumulative = CALCULATE(

    COUNT('Events table'[Event ID]),

    FILTER(

        ALL('Events table'),

        'Events table'[Date] <= MAX('Events table'[Date])

    )

)

	Count	Cumulative
01-Jun-20	2	2
02-Jun-20	3	5
02-Jun-20	1	6
Total	6	6

Displaying the cumulative number of cases each months is trivial using a DIMDATE table and the method used in the 'Cumulative' measure above. What I am trying to achieve is to display the average number of events that occurred each month based on the previous three years, i.e add all the cases in Jan-17, Jan-18 and Jan-19, divide it by three, and display the result as a forecast for January-20; then do something similar for all other months for an entire year.

I have tried everything I could think of for two days, and I am not getting anywhere.
Can anyone at least point me in the right direction?

Thanks

[Anonymous · Accepted solution]

Hi @lazarus1907 ,

Check the measure as below.

Measure = [Cumulative]/CALCULATE(DISTINCTCOUNT('Events Table'[Date]),FILTER(ALLSELECTED('Events Table'),'Events Table'[Date]<=MAX('Events Table'[Date])))

 

Best Regards,

Jay

[Anonymous]

the fastest way is to create 2 columns next to it 

first column

month = month(events table[date]) 

second column year 

year = Year(events table[date])

then create a measure 

2020 forecast events =  

VAR countyear = distinctcount(events table [year])  this will count the years to divide by 

VAR eventcount = distinctcount(event table[eventid]) should count the total number of events 

Return

eventcount / countyear 

this will give you an average and then in a matrix drop in month as row then drop in the 2020 forecast events in values and it should work 

[GilbertQ]

Hi there

What if you had to create a measure which would look at each quarter (Which would be the 3 months)

You could then take the result from the Quarter and calculate your forecast.

The DAX TotalQTD should work. Note you will need to have a date table for this to work

https://docs.microsoft.com/en-us/dax/totalqtd-function-dax