Rolling average on distinct count of doc nr

[Anonymous]

Dear all,

I have a list of order lines, and i would like to calculate the moving average (12 month) of orders that have been placed:

i have the following table:

DateDoc nrMonth

1-1-2020	0001	1
1-1-2020	0001	1
2-1-2020	0002	1
1-2-2020	0003	2
2-2-2020	0004	2
2-2-2020	0004	2
3-2-2020	0005	2
1-3-2020	0006	3
2-3-2020	0007	3
2-3-2020	0007	3
3-3-2020	0008	3
4-3-2020	0009	3
4-3-2020	0009	3

but somehow the following measure doesnt work:

12 month average =

CALCULATE(DISTINCTCOUNT('Table'[Doc nr]);DATESINPERIOD('Table'[Date];LASTDATE('Table'[Date]);-12;MONTH))

i tried to count the distinctcount of orders in the last twelve months. this gives me just the distinctcount of orders in that specific month.

 the requested results are in the last two columns in the table below

Month	Distinctcount	Distinctcount last twelve months	Moving average
1	2	2	2,00
2	3	5	2,50
3	4	9	3,00

[v-alq-msft · Accepted solution]

Hi, @Anonymous 

Based on your description, I created data to reproduce your scenario.

Table:

Calendar:

Calendar = CALENDARAUTO()

There is a many-to-one relationship between 'Table' and 'Calendar'.

You may create a calculated column and three measures as below.

Calculated column:
Monthnum = MONTH('Calendar'[Date])

Measure:
Distinctcount = DISTINCTCOUNT('Table'[Doc nr])

Distinctcount last 12 months = 
IF(
    NOT(ISBLANK([Distinctcount])),
    CALCULATE(
        DISTINCTCOUNT('Table'[Doc nr]),
        DATESINPERIOD(
            'Calendar'[Date],
            LASTDATE('Calendar'[Date]),
            -12,
            MONTH
        )
    )
)

Moving average = 
var monthnum = SELECTEDVALUE('Calendar'[Monthnum])
var _lastdistinct = 
CALCULATE(
    DISTINCTCOUNT('Table'[Doc nr]),
    FILTER(
        ALLSELECTED('Calendar'),
        'Calendar'[Monthnum] = monthnum-1
    )
)
return
IF(
    monthnum = 1,
    DIVIDE(
        [Distinctcount],
        1
    ),
    DIVIDE(
        [Distinctcount last 12 months],
        _lastdistinct
    )
)

Result:

Best Regards

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[parry2k]

@Anonymous for any time intelligence it is a best practice to add a calendar table and perform calculations from that. There are many posts that show you can add calendar/date dimensions in the model. 

I would 💖 Kudos 🙂 if my solution helped. If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

[Anonymous]

Thanks, so in my actual example im using a calendar dim table, but in this example i left it out.

with or without, still facing the problem.. lets say you replace the date field from the fact table with the calendar table.