Forum Discussion
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?
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.
8 Replies
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?
- 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?
