Count distinct R12

[Anonymous]

Spoiler

Hi, I have two col. Date and ID. I want for each date (day) make a distinct count for ID. R12. Twelve months back and rolling.

Plz help! 

Ex. data. 

Date	ID	Create_measure (count(distinct ID last 12 month (R12) for each date)
2021-08-24	101	8
2021-08-24	102	8
2021-08-24	103	8
2021-08-24	101	8
2021-08-25	201	8
2021-08-25	201	8
2021-08-25	202	8
2021-08-25	101	8
2021-09-05	101	8
2021-09-05	102	8
2021-09-05	106	8
2021-09-05	107	8
2021-09-05	108	8
…	…

[Anonymous]

Hi, Can someone plz help me find a solution @v-kelly-msft ? The file is uploaded. Thx! 🙂

[v-kelly-msft]

Hi  @Anonymous ，

First create a related date table and create a relationship between the 2 tables;

Then create a measure as below:

Measure =
CALCULATE (
    DISTINCTCOUNT ( 'Table'[ID] ),
    FILTER (
        ALL ( 'Table' ),
        'Table'[Date] <= MAX ( 'date table'[Date] )
            && 'Table'[Date] >= DATEADD ( 'date table'[Date], -12, MONTH )
    )
)

And you will see:

For the related .pbix file,pls see attached.

Best Regards,
Kelly

Did I answer your question? Mark my reply as a solution!

[Anonymous]

Some screendump

[Anonymous]

Hi! Sorry.. i can't get it to work as i want. I have my pbix-file but i cant attach it to the meassage. Plz help

[v-kelly-msft]

Hi  @Anonymous ,

Pls upload it to a cloud service then share a public link with us.

Remember to remove the confidential information.

Best Regards,
Kelly

Did I answer your question? Mark my reply as a solution!

[Anonymous]

Thx, its uploaded. https://drive.google.com/file/d/1E1DpRrBngzrKjHxIgvTRDuEZ71BZ6A_j/view?usp=sharing

[amitchandak]

@Anonymous , You have to create measures like these examples

Rolling 12 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date ],MAX('Date'[Date ]),-12,MONTH))

or

Rolling 12 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date ],MAX(Sales[Sales Date]),-12,MONTH))