RETURN VALUE FOR DATES IN GIVEN Start and End Dates

[someshwarrc]

Hi,

I have a table which is of the following format:

Start	End	Category
23-02-2021 00:10:00	24-03-2021 13:50:00	Category#1
24-02-2021 07:50:00	24-03-2021 13:50:00	Category#2
25-05-2021 06:00:00	25-05-2021 23:45:00	Category#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 (

    CROSSJOIN (

        CALENDAR ( DATE( 2022, 1, 1 ), DATE (2022, 12, 31 ) ),

       'Time'  

    ),

    "DateTime", [Date] + [Value]

)

VAR _start = CALCULATE(MIN(Deviations[Start]), Deviations[Category]="Category#1")

VAR _end = CALCULATE(MIN(Deviations[End]), Deviations[Category]="Category#1")

RETURN

ADDCOLUMNS(_calendar,

    "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. 

[amitchandak]

@someshwarrc , is it something like this with having table with date and time

Measure way
Power BI Dax Measure- Allocate data between Range: https://youtu.be/O653vwLTUzM
https://community.powerbi.com/t5/Community-Blog/How-to-divide-distribute-values-between-start-date-o...

Tables way
https://amitchandak.mediumcom/dax-get-all-dates-between-the-start-and-end-date-8f3dac4ff90b
https://amitchandak.medium.com/power-query-get-all-dates-between-the-start-and-end-date-9ad6a84cf5f2

[someshwarrc]

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 .