cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
leroy773
Helper II
Helper II

Search a specific date range without using date column as a filter

All,

 

I currently have a query with a date column.  These dates are based on edits.  I can create a duplicate column and tranform the dates to months to search solely by the month.  The issue I am having though is it will only report if there was an edit.  I am looking for a way for the power bi to show the last three months of status even if an edit was not done.

 

If I look at the report and report on the last three months:

 

Item A is shown because it was edited twice in that duration.

Item B is not shown because it was in the same status during that entire duration and no edits were made.

 

Is there a way to work around this?

1 ACCEPTED SOLUTION

That kept me entertained for an hour or so 🙂

 

https://www.dropbox.com/s/b1beaapdjtyp3ay/split%20days%20per%20month.pbix?dl=0

 

I am not saying this is the best way to do it, but it seems to work.  I created a pattern of 4 steps to extract the days for the current month and find any remainder.  I then copied these 4 steps multiple times (manually) to iterate, creating a new set of columns each time.  I created 1 extra set than needed allowing for growth.  The problem is that you will need to extend it further if a ticket is open for longer than 6 months.

 

Anyway, see what you think.



* Matt is a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.

View solution in original post

8 REPLIES 8

You should look at using a calendar table instead of converting your dates to months. http://exceleratorbi.com.au/power-pivot-calendar-tables/ 

 

Now you say that your date data only captures when there is an edit.  So do you have any date data other than this?  What is the logic that exisits in your data that can determine what to show for the last 3 months if it is not the Edit date column?  



* Matt is a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.

Unfortunately, do not have and other date fields that apply to this. There is a field that shows the duration it was in a certain status and not durations for past history. These are two different reports from salesforce. Was hoping there was a way to only show there months without using the edit date field. We currently do this with excel, which requires manual intervention. So hoping to find a way for it to be automated in BI.

You can only display based on the data you have. So how do you do it in Excel?



* Matt is a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.

That is what I thought.  In excel we export all the history data and perform the calculation in excel to show duration than go through the list and find where a status carried over an entire month an update that for the month.  Such as serial number 1 was in same status for 40 days.  For the month we report on we state it was in that status for 30 days.  Quite a manual process so was hoping BI had a solution.

Yes, BI has a solution.  You can create a new column in Power Query (get data).  You will need to rebuild the logic from Excel as an "Add Column" in Power Query.    If you don't know how to do that, you could post some sample data in an Excel workbook with the correct logic you are using for someone to take a look



* Matt is a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.

Thanks,  not sure how the logic would work.  In excel it was completely manual.  If this serial number was in a status for 40 days than for that one we new it was 100% and would carry the remainder to the next month.  Here is teh table I am working with, datediff is giving me the duration.  But for duration over one month in time or duration that carry from one month to the next, I am trying to figure out how to show that duration was associated with individual month.  The 122 date duration in my current report shows up to 122 days in April, when I would like it to show 122 days from April 19th till today.  So approximately 10 days in april, 30 days in may, 30 days in june and 30 days in july.  Roughly, have not refreshed report lately.

 

Serial NumberEdit DateOld ValueNew ValueIndexDuration
13/17/2016 8:22Non-OperationalFully Operational2711
13/28/2016 8:05Fully OperationalReduced Throughput282
13/30/2016 7:11Reduced ThroughputFully Operational299
14/8/2016 12:21Fully OperationalReduced Throughput304
14/12/2016 7:55Reduced ThroughputNon-Operational312
14/14/2016 11:13Non-OperationalFully Operational320
14/14/2016 11:54Fully OperationalNon-Operational335
14/19/2016 8:13Non-OperationalFully Operational34122
22/19/2016 16:40-Down for Maintenance3521
23/11/2016 13:53Down for MaintenanceFully Operational3621
24/1/2016 8:22Fully OperationalReduced Throughput370
24/1/2016 8:59Reduced ThroughputNon-Operational386
24/7/2016 7:53Non-OperationalFully Operational39134

That kept me entertained for an hour or so 🙂

 

https://www.dropbox.com/s/b1beaapdjtyp3ay/split%20days%20per%20month.pbix?dl=0

 

I am not saying this is the best way to do it, but it seems to work.  I created a pattern of 4 steps to extract the days for the current month and find any remainder.  I then copied these 4 steps multiple times (manually) to iterate, creating a new set of columns each time.  I created 1 extra set than needed allowing for growth.  The problem is that you will need to extend it further if a ticket is open for longer than 6 months.

 

Anyway, see what you think.



* Matt is a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.

View solution in original post

2016-08-19 12_23_43-BI RS Instrument Status History - Power BI Desktop.png

 

Current chart in report

Helpful resources

Announcements
MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Urdu Hindi D365 Bootcamp 768x460.png

Urdu Hindi D365 Bootcamp

Dont miss our very own April Dunnam’s The Developer Guide to the Galaxy! Find out what the Power Platform has to offer for the traditional developer.

Top Solution Authors
Top Kudoed Authors