Number of active day between a time frame

[Anonymous]

Hi all,

I am trying to calculate the number of days a machine is turned on between a period of time, please refer to the screenshot below. Much appreciated any suggesstion. Thanks

[Anonymous · Accepted solution]

Hi, @Anonymous 

Based on the information you have provided, Here are my answers to your questions.

1. Create a table based on your needs.

2. Write two calculated columns, enter the appropriate DAX code block and do a little integration to get Calculate No of active days.

No =

var _1=

    MAXX(FILTER('Table','Table'[Machine No]=EARLIER('Table'[Machine No])&&'Table'[_maxdateoff]=EARLIER('Table'[_maxdateoff])),'Table'[No of active days])

var __maxdateyear=

    YEAR('Table'[_maxdateoff])

var _dateyear=

    YEAR('Table'[Date])

return

IF('Table'[Active]="On",BLANK(),IF(YEAR(__maxdateyear)<>YEAR('Table'[Date]),_1))

No of active days =

var _maxdateoff=

   CALCULATE(min('Table'[Date]),FILTER('Table','Table'[Date]>=EARLIER('Table'[Date])&&'Table'[Active]="Off"&&'Table'[Machine No]=EARLIER('Table'[Machine No])))

var _1=

IF(YEAR(_maxdateoff)=YEAR('Table'[Date]),DATEDIFF('Table'[Date],_maxdateoff,DAY),IF(YEAR(_maxdateoff)=2023,DATEDIFF(DATE(2023,1,1),_maxdateoff,DAY),IF(YEAR(_maxdateoff)=2024,DATEDIFF('Table'[Date],DATE(2023,12,31),DAY))))

Return

_1

3. You can see the final result you need in the visualization.

How to Get Your Question Answered Quickly 

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards

Yilong Zhou

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

[Anonymous]

Hi Thanks so much it works as per description. However, is there a way to calculate the no of days based on the date filter rather than the fixed date.

e.g if filter is: 1/2/2023 to 1/10/2023 then the different would be between from the date minus 1/2/2023 (instead of the fixed date 1/1/2023), similarly with the 1/10/2023 instead of 31/12/2023.

Thanks heaps,

[Fowmy]

@Anonymous 

Can you share some sample data with the desired output to have a clear understanding of your question?
You can either paste your data in the reply box or save it in OneDrive, Google Drive, or any other cloud-sharing platform and share the link here.

[Anonymous]

Hi,

Thanks for looking into it.

Here is the data, the first 3 columns:

Machine No	Active	Date	Calculate No of active days	Note Only
ABC	On	7/05/2023
ABC	Off	9/07/2023	63
ABC	On	1/08/2023
ABC	Off	1/09/2023	31
ABC	On	10/09/2023
ABC	Off	12/12/2023	93
XYZ	On	5/03/2023
XYZ	Off	9/07/2023	126
DEF	On	5/08/2022
DEF	Off	9/07/2023	189	Since ON day is earlier than the filter range, active day = OFF date - From date (in filter)
DEF	On	1/08/2023
DEF	Off	1/09/2023	31
DEF	On	10/09/2023	112	Since OFF day is after the filter range, active day = To date (in filter) - ON date
DEF	OFF	10/04/2024

And here is the expected result when filter range is between 1/1/2023 to 31/12/2023

Machine No	No of active days
ABC	187
XYZ	126
DEF	313

Basically, the number of active day equal the date when machine is turn off minus the date when the machine is turn on. There are 2 exceptions:

• If the Off date is not in the filter range: active day = ON date minus the FROM date (in FIlter)
• If the ON date is not in the filter range: active day = TO date (in filter) minus OFF date.

Thanks,

[Anonymous]

Sorry got it the other way around for exception:

• If the Off date is not in the filter range: active day = TO date (in filter) minus OFF date
• If the ON date is not in the filter range: active day = ON date minus the FROM date (in Filter)