Calculate between time periode

DAX DAX Calculations
1

Hi,
I like to Count of Machine that are active in a time periode. I have a start date, and stop date, and i use a calender table. Th Count of items can be for each day, week, month, year.

I have a shrepoint teble With “machines”, and a table With “Activity” With machine name, start and stop date.

Example: Start Stop
Machine A 1. january 31. march
Machine B 5. january 15. february
Machine C 20. february 28. march
Machine D 1. april 30. april

When i use pr week in the calendar tape, i lake to se how many machines that was active in this actual day, week og month. If the time of calculation is between the stop and start date, it should be counted.
Jan Feb March April
Machine A 1 1 1 0
Machine B 1 1 0 0
Machine C 0 1 1 0
Machine D 0 0 0 1

Any idea how the DAX formula should look like. Thank You :-).

2

Hi @TrulsB,

Welcome to the forum!

How to resolve this will all depent on your model, so…

It’s important to provide as much context to a question as possible. This includes the data, the model, the formula, the filter context etc. And know that this forum is public. So if your data is considered sensitive - be sure to post a muck-up.

Since you have two dates to take into account, I can already suggest looking into this technique.

3

Test med datointervall.pbix (126.1 KB)

Here is the test DataModel. If we use context based on montly basis,
Machina A should be counted in for every date (day) between 1.january and 14. february;
Machine B should be counted in for every date between 1. january and 31.january,
Machine C should be counted in for every date between 15. february and 28. february AND 10 march and 31. march.
……
The intention is eg to se the time intervall between 4. january and 13. january. How many was active every day in that interval.

4

My intention is to create a graph and a table to se how many Machines that has been active every day, week, month.

5

Hi @TrulsB,

Thanks for the file and additional info! These are the steps I took.

  1. Marked your DateTable as a Date table

  2. Made both relationships between the Aktiviteter- and DateTable inactive

  3. Created the measure based on the technique referenced in the previous post

     Active Machine count = 
 CALCULATE(
     COUNTROWS( Aktiviteter ),
     FILTER( VALUES( Aktiviteter[StartDato] ), Aktiviteter[StartDato] <= MAX( DateTable[Date] )),
     FILTER( VALUES( Aktiviteter[StoppDato] ), Aktiviteter[StoppDato] >= MIN( DateTable[Date] )))

  4. Placed this measure in a table and a graph

    image
    image.png705×375 17.3 KB

Here’s the solution file, I hope this was helpful.
eDNA - Calculate between time periode.pbix (127.4 KB)

2 Likes
6

Thank you, Works perfect