Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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
amitchandak
Super User
Super User

@Anonymous 

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-trend/ba-p/882970

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-Difference-Across/ba-p/934397#M451

 

Anonymous
Not applicable

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.