Showing results for 
Search instead for 
Did you mean: 
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

PBI Sept Update Carousel

Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

Learn Live

Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

Dashboard in a day with date

Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

Top Solution Authors
Top Kudoed Authors