Skip to main content
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Frequent Visitor




I have a table which is of the following format:

23-02-2021 00:10:00       24-03-2021 13:50:00      Category#1
24-02-2021 07:50:0024-03-2021 13:50:00Category#2
25-05-2021 06:00:0025-05-2021 23:45:00Category#1


Now I have created a calendar table with a 10min interval and I want to plot a chart such that for the dates in the given periods I want to return a value when the date falls under any period in the provided table. For example, for the dates between 23-02-2021 00:10:00 to 24-03-2021 13:50:00 I want to return an integer 1 for each 10min interval otherwise 0 and for the dates between 24-02-2021 07:50:00 to 24-03-2021 13:50:00 I want to return an integer 2 otherwise 0. Such that plotting them with an Area chart it looks like the following:



Is this possible in Power BI for multiple such periods under at least 3 categories.  

Any help is highly appreciated! 


I found this link a bit helpful but the problem here is one category only appears once in the period(1 year) whereas in my problem it can appear multiple times. Any DAX or calculated columns would help.

Return a value if selected date is between two dates 



My current approach that I was trying after reading the above thread was the following, however this only gives me :


DateTime =
VAR _calendar = ADDCOLUMNS (
        CALENDAR ( DATE( 2022, 1, 1 ), DATE (2022, 12, 31 ) ),
    "DateTime", [Date] + [Value]
VAR _start = CALCULATE(MIN(Deviations[Start]), Deviations[Category]="Category#1")
VAR _end = CALCULATE(MIN(Deviations[End]), Deviations[Category]="Category#1")
    "Category#1Deviations", IF([DateTime] >= _start && [DateTime] <= _end, 1, 0)
Similarly I can do the Category#2 but it will only read the minimum dates from the columns. But the tuple of (start, end) dates may not always be the least in the columns for each Category unlike the link I shared. 

Kind of, but not quite. For example, I can have a measure or added column such that if the current datetime in question falls within the ranges for category 1, I must return a value such as 1 for the current datetime. The start and end act as tuples, I can't understand how to fetch those two together and in an iterative manner over the data because there can be multiple periods under each category. Your solution has only one category each and a start and end date to each. In this case there are multiple such ranges(start,end) to each category. Main problem I am facing is traversing the data iteratively to get a tuple/pair of start and end dates row wise from the data and then check the dates .

Helpful resources

Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City


Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

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

Top Solution Authors