cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

## Number of active day between a time frame

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

1 ACCEPTED SOLUTION
Community Support

Hi, @HKPBI

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.

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.

5 REPLIES 5
Community Support

Hi, @HKPBI

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.

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.

Frequent Visitor

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,

Super User

@HKPBI

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.

Did I answer your question? Mark my post as a solution! and hit thumbs up
Frequent Visitor

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,

Frequent Visitor

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)

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.