cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

Placing Counts in Days Buckets

https://drive.google.com/open?id=14L_DoJjh2Ifo-KLMJkcvurJSvZG5pvuu  Snapshot.PNGHi,

I have a

  • Data table with some caseid’s and dates.
  • Date table which has dates.

And established a relation based on date columns in these two tables.  

 

I want to calculate

  • Count of cases for current selected MonthEnd from the filter.
  • Count of cases 3 months prior to the selected MonthEnd from the filter.

Then place these counts in day buckets which is:

  • For a caseID the difference between start date and end date where ReservedOutcomeDate column being the start date and selected month end being the end date.  

I am able to do the count part but not days bucket correctly.

 

E.g.: Selected MonthEnd from filter is 31/01/2020.

  1. Days bucket - Selected Month End

 

Days bucket

0 - 30 days

31 - 90 days

91 - 180 days

181 - 365 days

12 - 18 mths

18 mths - 2 yrs

2 yrs +

 

  • Case Id 124 ReservedOutcomeDate is '2019-08-30' - Start Date
  • Selected MonthEnd is 31/01/2020 – End Date
  • Days Difference is 154 Days but result I am getting is falling in 0-30 days bucket.

 

  1. Days bucket for 3 months prior for selected month end.

 

Days

0 - 30 days

31 - 90 days

91 - 180 days

181 - 365 days

12 - 18 mths

18 mths - 2 yrs

2 yrs +

 

  • Case Id 124 ReservedOutcomeDate is '2019-08-30' - Start Date
  • Selected MonthEnd is 31/01/2020 so end date considered should be 31/10/2019.
  • Then Date Difference is 62 Days but result I am getting is falling in 0-30 days.

 

 

 

 

2 REPLIES 2
Highlighted
Super User IV
Super User IV

@SauravSidana 

Part of it is already covered in my blog. How to deal with start and end date

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

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

refer :https://community.powerbi.com/t5/Community-Blog/Decoding-Direct-Query-in-Power-BI-Part-2-Date-Differ...

 



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Highlighted

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 Capture.PNG

 

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors