Calculate difference per timeslot

[Ilse_ScpDt]

Hi there,

I am only familiair with simple dax so I am not sure how to calculate the following:

The difference between two values (a measure and a column) for every timeslot.

The thing is, this measure is calculating PM_Needed per timeslot if you put this together in a table. 

TimeSlot	PM_Needed	PM_Present	PM_Diff
07:30	1	1	0
07:45	1	1	0
08:00	1	2	1
08:15	2	2	0
08:30	2	2	0
08:45	2	3	1
09:00	3	3	0
09:15	3	3	0
09:30	3	3	0
09:45	3	3	0
10:00	3	3	0
10:15	3	3	0
10:30	3	3	0
10:45	3	3	0
11:00	3	3	0
11:15	3	3	0
11:30	3	3	0
11:45	3	3	0
12:00	3	3	0
12:15	3	3	0
12:30	3	3	0
12:45	3	3	0
13:00	3	2	-1
13:15	3	2	-1
13:30	3	3	0
13:45	3	2	-1
14:00	3	2	-1
14:15	3	3	0
14:30	3	2	-1
14:45	3	2	-1
15:00	3	3	0
15:15	3	3	0
15:30	3	3	0
15:45	3	3	0
16:00	3	3	0
16:15	3	3	0
16:30	3	3	0
16:45	3	3	0
17:00	3	3	0
17:15	3	3	0
17:30	2	3	1
17:45	1	2	1
18:00	1	1	0
18:15	1	1	0
18:30	1	1	0
Total	3	115	112

Now I would like to have 2 measures:

1. calculating the positive difference, in this case resulting in 4.

2. calculating the negative difference, in this case resulting in -6.

Timeslot is coming from Dim_Time and showing every minute.

PM_needed is based on a table with data per 15 minutes.

PM_present is a column from Fact_Staff.

Hope it is clear this way.

[v-jingzhang · Accepted solution]

Hi @Ilse_ScpDt 

You need to create a variable table in the measure to get the PM_Diff for every Timeslot. The variable table should be in the format of the sample table you showed in the original post. Then filter the variable table and count its positive and negative differences. 

Below are sample codes for your reference. The difficulty might be at creating the variable table. You may need to modify your code for PM_Needed, PM_Present and PM_Diff according to your model. 

Positive =
VAR vTable =
    ADDCOLUMNS (
        SUMMARIZE (
            'Dim_Time',
            'Dim_Time'[TimeSlot],
            "__PMNeeded", [PM_Needed],
            "__PMPresent", SUM ( 'Fact_Staff'[PM_present] )
        ),
        "__PMDiff", [__PMPresent] - [__PMNeeded]
    )
RETURN
    COUNTROWS ( FILTER ( vTable, [__PMDiff] > 0 ) )

Negative =
VAR vTable =
    ADDCOLUMNS (
        SUMMARIZE (
            'Dim_Time',
            'Dim_Time'[TimeSlot],
            "__PMNeeded", [PM_Needed],
            "__PMPresent", SUM ( 'Fact_Staff'[PM_present] )
        ),
        "__PMDiff", [__PMPresent] - [__PMNeeded]
    )
RETURN
    COUNTROWS ( FILTER ( vTable, [__PMDiff] < 0 ) )

Hope this helps.

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

[Ilse_ScpDt]

Now using this to sum PM_needed:

SumPM_Needed =

SUMX(

    FILTER(

          ALL(DIM_Time),

          DIM_Time[TimeSlot] <= MAX(DIM_Time[TimeSlot])

      ),

    [PM_Needed]

)

Still wondering how to get the positive and negative differences separately.