Forum Discussion
Determine if date is between 2 dates
Hi john_hqi,
I tested mmace1's idea. It works. Please have a try.
1. Create a table "Quarters".
2. Add a calculated column:
Column = CALCULATE ( VALUES ( Quarters[Value] ), FILTER ( 'Quarters', 'Quarters'[Start] <= EARLIER ( 'Calendar'[Date] ) && 'Quarters'[End] >= EARLIER ( 'Calendar'[Date] ) ) )3. If more quarters neeed, we can add to table "Quarters".
Best Regards!
Dale
Microsoft EmployeeHi john_hqi,
I tested mmace1's idea. It works. Please have a try.
1. Create a table "Quarters".
2. Add a calculated column:
Column =
CALCULATE (
VALUES ( Quarters[Value] ),
FILTER (
'Quarters',
'Quarters'[Start] <= EARLIER ( 'Calendar'[Date] )
&& 'Quarters'[End] >= EARLIER ( 'Calendar'[Date] )
)
)3. If more quarters neeed, we can add to table "Quarters".
Best Regards!
Dale
This looks like it will work thanks everyone!
Not being able to get this to work. Adding a New Column to calcaute the Period a date falls between and getting error - table of multiple vaules was supplied when a single value was expected.
Date Period = CALCULATE (VALUES (Period[PERIOD]),FILTER (Period,Period[START_DATE]<=EARLIER(SCOMMON[Dates])&&Period[END_DATE]>=EARLIER(SCOMMON[Dates])))
- Ashish_Mathur
Super User
Hi,
Share the link from where i can download your file.
Hi v-jiascu-msft,
I have bit simillar requirment. I have to calucalte quaterly sales perfromance of the companey. But My boss want quarter should start from first monday of the quarter. rang is like that.
- v-jiascu-msft
Hi Anonymous,
Please open a new thread. One thread one topic and this thread has been closed. Thanks for your understanding.
Best Regards,
Dale
SilkoHelper II
- v-jiascu-msft
Hello v-jiascu-msft ,
Your solution may help my case too. Can you please tell me what is the relationship between the two tables (quarters and calendar tables) you are using? (i.e. what datafields in the two tables you are using to connect the two tables?)
Everything works fine for me in the formula until the function EARLIER. Just after the EARLIER function when I am trying to type the date field from my calendar table I don't have the list of the available data fields to choose it. I believe I did not connected the two tables properly. I tried to connect the field "Start date" in Quarters table with field "Date" in date teble (not sure if this is correct).
Many thanks
- v-jiascu-msft
Hi Anonymous,
Actually, no relationship is there. No EARLIER is needed either. Please refer to the file in the attachment. I have improved it.
Best Regards,
Dale
Is there a way to do this with DAX?
