I am trying to accomplish some tricky date logic. Here is how it breaks down. Units in our operating system can go down for maitenance. We have a report that tacks this. I am trying to get my data to display the same down unit counts as the report. The report looks at the date the unit went down to the date that the unit goes back up. It pulls in anything in between. My query pulls all the data, I have tied that part out, but the date filters is what is screwing me up. For example if my go down date is 1/1/2019 and my go up date is 1/31/2019 anything that has a start date or an end date in that range will show up. Also and this is the tricky part, If a unit went down on 11/30/2018 and came up 1/5/2019 it will not show up with my filters. If it went down 12/1/2018 and came up 2/4/2019 it also will not show. I know what the logic needs to be I just do not know how to implament it. I have created a dynamic calendar table but so far it is not helping me.
This is the logic: Down Start <= End of Month = True and Down End >= Begin of Month = True
Here is an example of how it works.
Hi @reh169 ,
From your sample data, I could not understand which month of the date is your standard date?
Also you could refer to below steps to get your start of month and end of the month:
Apply it and use below formulas:
Column1 = IF('Table1'[DownStart]<=[End of Month],TRUE())
Column2 = IF('Table1'[Downend]>=[Start of Month],TRUE())
You could aslo download the pbix file to have a view.
Daniel, what do you mean by starndard date? I have a column that lets me if both are met but how do I use that to create a date filter is my issue. Everything ends up wtih a 1. I need a date filter that will allow me more flexibility. So that I can catch anything that falls within my time frame. For Example if I have 1/31/2019 and the date that the unit went down and 1/31/2019 as date when the unit went up. I still want to see the unit that went down on 12/5/2018 and came up on 1/15/2019. And the one that went down on 1/04/2019 and came up on 2/7/2019. So far it seems that if both the start and end date do not fall with in the range there is no way to capture the ones that fall during but have a start or end date outside of the range. Does that make sense?
Hi @reh169 ,
From your sample data, could you want to calculate the [Start of Month] and the [End of the Month] and compare it with your date column? Your formula:
if('Down US'[DateStart]<='Down US'[End Start of Month]&&'Down US'[DateEnd]>='Down US'[Start End of Month],1,0)
It could work if all the columns is clearly, right? So I want to know the End of Month and the Start of Month should be calculated with which column? DateStart or DateEnd? I suggest you give a sample data and your desired result if possible?
Here is a sample of the data. The formula I listed is the condition that the date parameters needs to account for. I am not sure how to do that. So for the given data set below if my paramers for start and end date are 3/1/2016 to 3/31/2018 I would expect the the first line, the 3-8 lines to show. Even though their start and end dates might be outside my range they are down during that period. I just do not know how to make a date filter that will use this logic.