Forum Discussion
Helper ICalculate difference per timeslot
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.
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.
2 Replies
Ilse_ScpDtNow 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.
v-jingzhangCommunity Support
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.