How Long Between Dates

[Anonymous]

Hi,

I am trying to create a visualization that shows Number of Open Leaks by day... 

In the WR_NO below, the leak was open from 4/2/2017-4/17/2017. 

When putting it into a graph, it only shows the Date_Opened Date, and the number of days it was "Open"...

Is there a way to show the leak being open on 4/2 thru 4/17? So, for the dates 4/2-4/17, the value would be 1... and I would do this for all WR_NO's, so the values displayed in the visual would be the days that the WR_NO's are open

Please let me know if this question does not make sense

Thank you!
Sarah

[amitchandak · Accepted solution]

@Anonymous , Refer if this blog can help: https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-trend/ba-p/882970

Check this file :https://www.dropbox.com/s/bqbei7b8qbq5xez/leavebetweendates.pbix?dl=0

[Anonymous]

hi @amitchandak this is amazing! the only issue i am running into is that it will not allow me to display multiple WRs...

for example, in the below screenshot, i have created measures like you had shown me, and it displays the Sum of Days from 3/13-3/31.

when I add another WR# with the same date range to test it, it does not show anything... is there a way to display multiple WR_NOs in one visualization with the sum of days for those WR_NOs?

I eventually want to create a bar chart displaying dates on the x-axis and sum of days on the y axis

thank you!
Sarah

[Anonymous]

sorry the screenshots were not attached

please see below

[amitchandak]

@Anonymous , Which one you used. I shared two test cases. please share the formula. Better is that if you can share a few rows of data in table format. We will try to provide the formula based on that.

[Anonymous]

@amitchandak , These are the formulas I used:

Completed WRss = CALCULATE(COUNT(Query1[WR_NO]),USERELATIONSHIP(Query1[REPAIR_DATE],'2014 to Present'[Date]),not(ISBLANK(Query1[DATE_OPENED])))

Current Open Wrss = CALCULATE(COUNTx(FILTER(Query1,Query1[DATE_OPENED]<=max('2014 to Present'[Date]) && (ISBLANK(Query1[DATE_OPENED]) || Query1[REPAIR_DATE]>max('2014 to Present'[Date]))),(Query1[WR_NO])),CROSSFILTER(Query1[DATE_OPENED],'2014 to Present'[Date],None))

WRss? = CALCULATE(COUNT(Query1[WR_NO]),USERELATIONSHIP(Query1[DATE_OPENED],'2014 to Present'[Date]) )

WR Changee% = if(not(ISBLANK([Last Year WRss?] )),CALCULATE( (divide([Current Open Wrss],[Last Year WRss?] ) -1)*100))

Days for WR = [Current Open Wrss]+[Completed WRss]

Sum days = sumx('2014 to Present',[Days for WR])

In the table, I am only using the "Sum days" formula, because this displays the values that I want

[Anonymous]

@amitchandak 

[Anonymous]

WR_NO REPAIR_DATE DATE_OPENED   3599101 4/2/2020 0:00 3/23/2020 0:00   3597493 4/1/2020 0:00 3/15/2020 0:00   3597485 4/1/2020 0:00 3/14/2020 0:00   3598391 4/1/2020 0:00 3/17/2020 0:00   3597219 3/31/2020 0:00 3/12/2020 0:00   3597487 3/31/2020 0:00 3/13/2020 0:00   3597558 3/31/2020 0:00 3/13/2020 0:00   3596836 3/30/2020 0:00 3/11/2020 0:00   3597249 3/27/2020 0:00 3/10/2020 0:00

@amitchandak here is some sample data

[amitchandak]

@Anonymous ,

Please find the attached solution after the signature. 

[Greg_Deckler]

Not certain, but it sounds like you want Open Tickets, it was designed to work with date intervals.

https://community.powerbi.com/t5/Quick-Measures-Gallery/Open-Tickets/td-p/409364