Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Don't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.

Reply
pavithrarajan
Frequent Visitor

I want to create a measure to say "yes" or "no" checking date between start and end date

‎12-16-2024 03:01 PM

I want to create a measure to say "yes" or "no".
Whether the date is falling between start and end date. 

I have column "dataonly", - this column needs to be checked whether the date is falling between startdate adn enddate.
As a outcome, if yes - then the measure should show yes, no mean no.

I need a measure because this is a visual will be changing dyanmically. So i need a measure that dynamically says yes or no.

pavithrarajan_0-1734389907205.png

 

Labels:
Message 1 of 7
284 Views
0
1 ACCEPTED SOLUTION
v-rzhou-msft
Community Support
Community Support
In response to pavithrarajan

‎12-16-2024 09:18 PM

Hi @pavithrarajan ,

 

If you want to check whether the date is between range dynamiclly by slicer or filter, I suggest you to create an unrelated DimPeriod table to help calculation.

 

DimPeriod = FILTER(VALUES('Time'[Period]),[Period]<>BLANK())

 

Then create a measure to achieve your goal.

 

Measure = 
VAR _TimeList = CALCULATETABLE('Time',FILTER(ALLSELECTED('Time'),'Time'[Period] in VALUES('DimPeriod'[Period])))
VAR _Flag = ADDCOLUMNS(_TimeList,"Flag",IF(MAX(A[DateOnly]) >= [StartDate] && MAX(A[DateOnly]) <= [EndDate],1,0))
RETURN
IF(SUMX(_Flag,[Flag]) = 1,"Yes","No")

 

Result is as below.

vrzhoumsft_0-1734405320189.png

 

Best Regards,
Rico Zhou

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

View solution in original post

checking date between start and end date.pbix
Message 7 of 7
145 Views
0
6 REPLIES 6
danextian
Super User
Super User

‎12-16-2024 03:09 PM

Hi @pavithrarajan 

Assuming all these columns are from the same table:

IF (
    SELECTEDVALUE ( 'table'[DateOnly] ) >= SELECTEDVALUE ( 'table'[StartDate] )
        && SELECTEDVALUE ( 'table'[DateOnly] ) <= SELECTEDVALUE ( 'table'[EndDate] ),
    "Yes",
    "no"
)

Otherwise, please provide a workable sample data (not an image) and more context.










Did I answer your question? Mark my post as a solution!


Proud to be a Super User!









"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu or follow my page on Facebook @DAXJutsuPBI.
Message 2 of 7
258 Views
0
pavithrarajan
Frequent Visitor
In response to danextian

‎12-16-2024 03:57 PM

Table A:

NameDate modifiedDateOnly
d10/01/2024 13:0310-Jan-24
d10/01/2024 13:4810-Jan-24
d08/11/2023 08:5208-Nov-23
d08/11/2023 08:5208-Nov-23
d08/11/2023 08:5208-Nov-23
d08/11/2023 08:5208-Nov-23
d08/11/2023 08:5208-Nov-23
d08/11/2023 08:5208-Nov-23
d06/12/2023 09:4506-Dec-23
d06/12/2023 09:4506-Dec-23
d06/12/2023 09:4506-Dec-23
d06/12/2023 09:4506-Dec-23
d06/12/2023 09:4506-Dec-23
d04/01/2024 05:2404-Jan-24
d31/01/2024 12:3931-Jan-24
d05/01/2024 07:5505-Jan-24
d07/02/2024 00:4107-Feb-24
d05/01/2024 07:5505-Jan-24
d05/01/2024 07:5505-Jan-24
d05/01/2024 07:5505-Jan-24
d29/02/2024 02:5229-Feb-24
d01/02/2024 12:1001-Feb-24
d01/02/2024 12:1001-Feb-24
d01/02/2024 12:1001-Feb-24
d02/02/2024 10:1602-Feb-24
d28/02/2024 10:0028-Feb-24
d29/02/2024 08:5729-Feb-24
d28/02/2024 10:0028-Feb-24
d28/02/2024 10:0028-Feb-24
d28/02/2024 10:0028-Feb-24
d29/02/2024 02:4229-Feb-24
d07/11/2023 17:1007-Nov-23
d08/11/2023 09:3708-Nov-23
d05/12/2023 18:1105-Dec-23
d10/01/2024 13:5110-Jan-24
d03/01/2024 10:2603-Jan-24
d10/01/2024 13:5210-Jan-24
d30/01/2024 18:2130-Jan-24
d06/02/2024 12:2406-Feb-24
d27/02/2024 18:0127-Feb-24
d19/01/2024 10:1519-Jan-24
d19/01/2024 10:1019-Jan-24
d19/01/2024 10:0619-Jan-24
d30/01/2024 08:4030-Jan-24
d10/01/2024 09:5110-Jan-24
d22/12/2023 15:4922-Dec-23
d01/11/2023 20:0101-Nov-23
d30/11/2023 19:2430-Nov-23
d13/12/2023 10:4013-Dec-23
d02/01/2024 21:0702-Jan-24
d24/01/2024 17:0324-Jan-24
d30/01/2024 12:2730-Jan-24
d31/01/2024 13:1631-Jan-24
d16/02/2024 13:2016-Feb-24
d19/02/2024 12:4419-Feb-24
d19/02/2024 16:5719-Feb-24
d19/02/2024 17:0219-Feb-24
a01/11/2023 19:4501-Nov-23
c10/11/2023 16:3710-Nov-23
a04/12/2023 13:5304-Dec-23
a08/12/2023 11:0008-Dec-23
a10/01/2024 09:5210-Jan-24
c08/12/2023 14:1708-Dec-23
a02/01/2024 21:3302-Jan-24
c05/01/2024 06:4305-Jan-24
a24/01/2024 16:5924-Jan-24
c02/02/2024 15:3502-Feb-24
a19/02/2024 15:5819-Feb-24


table PERIOD:

Date DateOnly period
03/12/2023 00:00 03-Dec-23 23_13
04/12/2023 00:00 04-Dec-23 23_13
05/12/2023 00:00 05-Dec-23 23_13
06/12/2023 00:00 06-Dec-23 23_13
07/12/2023 00:00 07-Dec-23 23_13
08/12/2023 00:00 08-Dec-23 23_13
09/12/2023 00:00 09-Dec-23 23_13
10/12/2023 00:00 10-Dec-23 23_13
11/12/2023 00:00 11-Dec-23 23_13
12/12/2023 00:00 12-Dec-23 23_13
13/12/2023 00:00 13-Dec-23 23_13
14/12/2023 00:00 14-Dec-23 23_13
15/12/2023 00:00 15-Dec-23 23_13
16/12/2023 00:00 16-Dec-23 23_13
17/12/2023 00:00 17-Dec-23 23_13
18/12/2023 00:00 18-Dec-23 23_13
19/12/2023 00:00 19-Dec-23 23_13
20/12/2023 00:00 20-Dec-23 23_13
21/12/2023 00:00 21-Dec-23 23_13
22/12/2023 00:00 22-Dec-23 23_13
23/12/2023 00:00 23-Dec-23 23_13
24/12/2023 00:00 24-Dec-23 23_13
25/12/2023 00:00 25-Dec-23 23_13
26/12/2023 00:00 26-Dec-23 23_13
27/12/2023 00:00 27-Dec-23 23_13
28/12/2023 00:00 28-Dec-23 23_13
29/12/2023 00:00 29-Dec-23 23_13
30/12/2023 00:00 30-Dec-23 23_13
01/12/2023 00:00 01-Dec-23 23_12
02/12/2023 00:00 02-Dec-23 23_12
31/12/2023 00:00 31-Dec-23 23_01
28/01/2024 00:00 28-Jan-24 24_02
29/01/2024 00:00 29-Jan-24 24_02
30/01/2024 00:00 30-Jan-24 24_02
31/01/2024 00:00 31-Jan-24 24_02
01/01/2024 00:00 01-Jan-24 24_01
02/01/2024 00:00 02-Jan-24 24_01
03/01/2024 00:00 03-Jan-24 24_01
04/01/2024 00:00 04-Jan-24 24_01
05/01/2024 00:00 05-Jan-24 24_01
06/01/2024 00:00 06-Jan-24 24_01
07/01/2024 00:00 07-Jan-24 24_01
08/01/2024 00:00 08-Jan-24 24_01
09/01/2024 00:00 09-Jan-24 24_01
10/01/2024 00:00 10-Jan-24 24_01
11/01/2024 00:00 11-Jan-24 24_01
12/01/2024 00:00 12-Jan-24 24_01
13/01/2024 00:00 13-Jan-24 24_01
14/01/2024 00:00 14-Jan-24 24_01
15/01/2024 00:00 15-Jan-24 24_01
16/01/2024 00:00 16-Jan-24 24_01
17/01/2024 00:00 17-Jan-24 24_01
18/01/2024 00:00 18-Jan-24 24_01
19/01/2024 00:00 19-Jan-24 24_01
20/01/2024 00:00 20-Jan-24 24_01
21/01/2024 00:00 21-Jan-24 24_01
22/01/2024 00:00 22-Jan-24 24_01
23/01/2024 00:00 23-Jan-24 24_01
24/01/2024 00:00 24-Jan-24 24_01
25/01/2024 00:00 25-Jan-24 24_01
26/01/2024 00:00 26-Jan-24 24_01
27/01/2024 00:00 27-Jan-24 24_01


 

TABLE TIME:

PeriodStartDateEndDate
23_1010-Sep-2307-Oct-23
23_1108-Oct-2304-Nov-23
23_1205-Nov-2302-Dec-23
23_1303-Dec-2330-Dec-23
24_0131-Dec-2327-Jan-24
24_0228-Jan-2424-Feb-24
24_0325-Feb-2423-Mar-24
24_0424-Mar-2420-Apr-24
24_0521-Apr-2418-May-24
24_0619-May-2415-Jun-24
24_0716-Jun-2413-Jul-24
24_0814-Jul-2410-Aug-24
24_0911-Aug-2407-Sep-24
24_1008-Sep-2405-Oct-24
24_1106-Oct-2402-Nov-24
24_1203-Nov-2430-Nov-24
   


MODEL:

pavithrarajan_1-1734393456361.png


vISUAL"

 

pavithrarajan_0-1734393436487.png

 



Message 6 of 7
216 Views
0
v-rzhou-msft
Community Support
Community Support
In response to pavithrarajan

‎12-16-2024 09:18 PM

Hi @pavithrarajan ,

 

If you want to check whether the date is between range dynamiclly by slicer or filter, I suggest you to create an unrelated DimPeriod table to help calculation.

 

DimPeriod = FILTER(VALUES('Time'[Period]),[Period]<>BLANK())

 

Then create a measure to achieve your goal.

 

Measure = 
VAR _TimeList = CALCULATETABLE('Time',FILTER(ALLSELECTED('Time'),'Time'[Period] in VALUES('DimPeriod'[Period])))
VAR _Flag = ADDCOLUMNS(_TimeList,"Flag",IF(MAX(A[DateOnly]) >= [StartDate] && MAX(A[DateOnly]) <= [EndDate],1,0))
RETURN
IF(SUMX(_Flag,[Flag]) = 1,"Yes","No")

 

Result is as below.

vrzhoumsft_0-1734405320189.png

 

Best Regards,
Rico Zhou

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

checking date between start and end date.pbix
Message 7 of 7
146 Views
0
pavithrarajan
Frequent Visitor
In response to danextian

‎12-16-2024 03:37 PM

 i am not able to add pbix or excel here

 

Message 3 of 7
242 Views
0
danextian
Super User
Super User
In response to pavithrarajan

‎12-16-2024 08:53 PM

I dont get the use of Table Period but is this what you're looking for?

danextian_0-1734411183329.png

 










Did I answer your question? Mark my post as a solution!


Proud to be a Super User!









"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu or follow my page on Facebook @DAXJutsuPBI.
Message 5 of 7
158 Views
0
danextian
Super User
Super User
In response to pavithrarajan

‎12-16-2024 07:33 PM

You can post a link to a cloud storage.










Did I answer your question? Mark my post as a solution!


Proud to be a Super User!









"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu or follow my page on Facebook @DAXJutsuPBI.
Message 4 of 7
178 Views
0

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.

Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan25PBI_Carousel

Power BI Monthly Update - January 2025

Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.

View All