Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
https://drive.google.com/open?id=14L_DoJjh2Ifo-KLMJkcvurJSvZG5pvuu Hi,
I have a
And established a relation based on date columns in these two tables.
I want to calculate
Then place these counts in day buckets which is:
I am able to do the count part but not days bucket correctly.
E.g.: Selected MonthEnd from filter is 31/01/2020.
Days bucket
0 - 30 days |
31 - 90 days |
91 - 180 days |
181 - 365 days |
12 - 18 mths |
18 mths - 2 yrs |
2 yrs + |
Days
0 - 30 days |
31 - 90 days |
91 - 180 days |
181 - 365 days |
12 - 18 mths |
18 mths - 2 yrs |
2 yrs + |
@Anonymous
Part of it is already covered in my blog. How to deal with start and end date
For date bucket
Measure =
maxx(summarize(Table,table[Caseid],"_diff", datediff(mix(table[start date]),max(Date[Date]))),
switch( true(),
_diff <=30 , "0 - 30 days",
_diff >30 && _diff<90, "31 - 90 days",
_diff >91 && _diff<180, "91 - 180 days",
_diff >181 && _diff<356, "181 - 365 days",
_diff >365 && _diff<365+180, "12 - 18 mths",
_diff >365 && _diff<365+365, "18 mths - 2 yrs",
"2 yrs +"
))
Row context is important
Hi Amit,
The solution you have provided for the measure only works for CaseId having the ReservedOutcomeDate in current month.
For CaseId having ReservedOutcomeDate before the selected month, the bucket is blank.
Can you have a look or paste an updated pbix file. I am attaching my version of the file.
Thanks,
Saurav Sidana
https://drive.google.com/open?id=1-wn0pKOD6U7dJ950e3wAuzlolyU4L4zQ
User | Count |
---|---|
141 | |
113 | |
104 | |
77 | |
64 |
User | Count |
---|---|
135 | |
123 | |
101 | |
71 | |
61 |