Forum Discussion
Dynamic Date Filters
Hi Anonymous ,
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())
Result:
You could aslo download the pbix file to have a view.
Regards,
Daniel He
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 Anonymous ,
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?
Regards,
Daniel He
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.