The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
Hi Guys,
I am learning in Power BI, Would appreciate your help on below query, I have a tabe like this and I have to find average of these ID's(i.e average of count of ID's) for last 6 months,
Thank you!
ID | DATE |
1 | 1/10/2021 |
2 | 1/11/2021 |
3 | 2/11/2021 |
4 | 3/11/2021 |
5 | 4/11/2021 |
6 | 1/12/2021 |
7 | 2/12/2021 |
8 | 1/01/2022 |
9 | 2/01/2022 |
10 | 3/02/2022 |
11 | 4/03/2022 |
12 | 5/03/2022 |
13 | 11/04/2022 |
14 | 12/04/2022 |
@Anonymous , Try measure like
Rolling 6 Sales =
var _max = maxx(allselcted(date),date[date]) // or today()
var _min = date(year(_max), month(_max)-6,1)
return
CALCULATE(distinctcount(Table[ID]),filter(date, date[date] <=_max && date[date] >=_min))
or
Rolling 6 = CALCULATE(distinctcount(Table[ID]),DATESINPERIOD('Date'[Date ],MAX('Date'[Date ]),-6,MONTH))
User | Count |
---|---|
11 | |
8 | |
6 | |
6 | |
6 |
User | Count |
---|---|
23 | |
14 | |
13 | |
10 | |
8 |