Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi All,
Help Needed.
I have a table like below:
Date1 date 2 ID Value
1/2/2019 5/2/2019 30 12
2/2/2019 5/2/2019 30 10
3/2/2019 5/2/2019 31 23
4/2/2019 5/2/2019 45 24
2/2/2019 6/2/2019 45 45
3/2/2019 6/2/2019 45 45
4/2/2019 6/2/2019 5 56
5/2/2019 6/2/2019 6 78
I want to perform the below steps:
1.Groupby date1 on max(date2)
2.create a line graph which plots the 12 months cumulative aggregate of value for the year(max(date 2).Here it is 2019
3.I want to add a slicer for ID which will make the line graph gets filtered for each ID selected.When i do groupby on dates the ID column is getting removed so am not able to establish a relation with other tables having ID column.That is i want the resulting table after groupby should contain a unique column to establish relationship.
Kindly help.Stuck up with this since 3days.
Solved! Go to Solution.
HI @Suryaann ,
You can use following measure formula to calculate cumulative average based on current 'date 2' group and year:
Measure = VAR currDate = MAX ( Table[Date2] ) RETURN CALCULATE ( AVERAGE ( Table[Value] ), FILTER ( ALLSELECTED ( Table ), [Date2] = currDate && [Date1] < currDate && YEAR ( Table[Date1] ) = YEAR ( currDate ) ), VALUES ( Table[ID] ) )
Regards,
Xiaoxin Sheng
HI @Suryaann ,
You can use following measure formula to calculate cumulative average based on current 'date 2' group and year:
Measure = VAR currDate = MAX ( Table[Date2] ) RETURN CALCULATE ( AVERAGE ( Table[Value] ), FILTER ( ALLSELECTED ( Table ), [Date2] = currDate && [Date1] < currDate && YEAR ( Table[Date1] ) = YEAR ( currDate ) ), VALUES ( Table[ID] ) )
Regards,
Xiaoxin Sheng
When you group by Date1, you want the max of Date2 returned. Got that. What happens when Date1 is the same but the ID is different? Wouldn't grouping by Date1 get confused about what to do with the ID column?
User | Count |
---|---|
124 | |
108 | |
99 | |
62 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |