DAX to calculate whether the sum for the 3 days is above 72 hours

[Anonymous]

Hi everyone,

I am trying to build a measure to see whether the sum of a column for the last 3 days is greater than 72 days. If it is, then I need to ignore them from my Month-Year total.

Example in the table below, as the sum of 1/5 to 3/5 is >= 72, I need to show 0 as the value. Where the sum is < 72, e.g. 2/5 to 4/5 is 63 then I need to show 15 etc. I then need to sum up all the values for a Month-Year total i.e. 43.5 below.

I have tried using DATESINPERIOD as below and it sums it correctly at a date level. I am unsure how to ignore values above 72 and sum the Month-Year to 43.5

CALCULATE(SUM(Actualhours),

   DATESINPERIOD(Date[Date], MIN(Date[Date], -3, DAY)

)

I also need to add a filter to the measure to exclude a value but I can't seem to be able to use DATESINPERIOD and FILTER together as.

CALCULATE(SUM(Actualhours),

   DATESINPERIOD(Date[Date], MIN(Date[Date], -3, DAY),

   FILTER(Table1, Table1[ColumnA] <> "ABC")

)

Any suggestions?

YearMonth	Date	ActualHours	SumOfLast3Days	ValueNeeded
May-18	1/05/2018	24	24	0
May-18	2/05/2018	24	48	0
May-18	3/05/2018	24	72	0
May-18	4/05/2018	15	63	15
May-18	5/05/2018	14	53	14
May-18	6/05/2018	14.5	43.5	14.5
May-18	7/05/2018	24	52.5	0
May-18	8/05/2018	24	62.5	0
May-18	9/05/2018	24	72	0
		187.5		43.5

[Ashish_Mathur]

Hi,

Share the link from where i can download your PBI file.

[Anonymous]

https://1drv.ms/u/s!ArxsIP-W81kFgppleAo0lyKh8f-hTQ

[Ashish_Mathur]

Hi,

I think your replaces measure should be this

=SUMX(SUMMARIZE(VALUES(MasterCalendar[Date]),[Date],"ABCD",if(CALCULATE(SUM(TableFromExcel[ActualHours]),DATESBETWEEN(MasterCalendar[Date],MIN(MasterCalendar[Date])-3,MIN(MasterCalendar[Date])))>=72,0,SUM(TableFromExcel[ActualHours]))),[ABCD])

Also, remove Shift Date from the Row labels section of the visual and drag Date from the MasterCalendar Table instead.

[Anonymous]

Thanks. That worked as expected for all but 1 cell. Can you explain the logic of using "ABCD"?

I added in a filter so that the measure only looks at Planned Maintenance as I want take away the need of  a slicer. My adjusted measure below.

With your measure or mine with the added filter, one cell isn't calulcated as expected. See picture below. Given 25/05, 26/05 and 27/05 sum to 52.65, I would expect 27/05 to show 4.65 in [ReplaceNew]. Any reason why this isn't the case?

ReplaceNew =
    SUMX(SUMMARIZE(VALUES(MasterCalendar[Date]),[Date],"ABCD",
        IF(CALCULATE(SUM(TableFromExcel[ActualHours]),
            DATESBETWEEN(MasterCalendar[Date], MIN(MasterCalendar[Date])-3, MIN(MasterCalendar[Date]))) >= 72, 0,
               CALCULATE(SUM(TableFromExcel[ActualHours]), FILTER(TableFromExcel, TableFromExcel[Category] = "Planned Maintenance")))
        ),[ABCD]
    )

[Ashish_Mathur · Accepted solution]

Hi,

Does this work?

ReplaceNew =
    SUMX(SUMMARIZE(VALUES(MasterCalendar[Date]),[Date],"ABCD",
        IF(CALCULATE(SUM(TableFromExcel[ActualHours]),
            DATESBETWEEN(MasterCalendar[Date], MIN(MasterCalendar[Date])-2, MIN(MasterCalendar[Date]))) >= 72, 0,
               CALCULATE(SUM(TableFromExcel[ActualHours]), TableFromExcel[Category] = "Planned Maintenance"))
        ),[ABCD]
    )

[Anonymous]

I changed your measure slightly as below and works as expected. Thanks.

Could you explain the logic around "ABCD"?

ReplaceNew = 
SUMX(

  SUMMARIZE(

    VALUES(MasterCalendar[Date]),[Date],"ABCD", 

      IF(CALCULATE(SUM(TableFromExcel[ActualHours]), 

        DATESBETWEEN(MasterCalendar[Date], MIN(MasterCalendar[Date]) - 2, MIN(MasterCalendar[Date]))) >= 72,

        0, CALCULATE(SUM(TableFromExcel[ActualHours]), FILTER(TableFromExcel, TableFromExcel[Category] = "Planned Maintenance")

      )

    )

  ), ABCD]

)

[Ashish_Mathur]

Hi,

ABCD is the title of the virtual column created via the SUMMARIZE function.

[v-shex-msft]

Hi @Anonymous,

You can try to use below measrue to achieve your requiremnt:

Replaced Value = 
VAR currDate =
    MAX ( Table1[Date] )
VAR rolling =
    CALCULATE (
        SUM ( [ActualHours] ),
        FILTER ( ALL ( Table1 ), [Date] > currDate - 3 && [Date] <= currDate )
    )
RETURN
    IF (
        rolling < 72
            && COUNTROWS ( FILTER ( ALLSELECTED ( Table1[Date] ), [Date] <= currDate ) )
                >= 3,
        SELECTEDVALUE ( Table1[ActualHours] ),
        0
    )

Rolling value = 
SUMX (
    FILTER ( ALLSELECTED ( Table1 ), [Date] <= MAX ( [Date] ) ),
    [Replaced Value]
)

Regards,

Xiaoxin Sheng

[Anonymous]

Thanks Xiaoxin. I have applied you logic, however, I am getting zeros in the 1st measure and the 2nd measure is taking too long to calculate:

I have used a MasterCalendar Date column instead of the table Date column which has a one to many relationship. I have applied the measure as below and the result is given 0 to all dates included the YearMonth column. See below.

When I add the measure [Rolling value] to the table, it's extremely slow to calculate. Measure is

Replaced Value =
VAR currDate =
    MAX ( PBI_MASTER_CALENDAR[Date] )
VAR rolling =
    CALCULATE (
        SUM ( Table1[ActualHours] ),
        FILTER ( ALL ( PBI_MASTER_CALENDAR ), [Date] > currDate - 3 && [Date] <= currDate )
    )
RETURN
    IF (
        rolling < 72
            && COUNTROWS ( FILTER ( ALLSELECTED ( PBI_MASTER_CALENDAR[Date] ), [Date] <= currDate ) )
                >= 3,
        SELECTEDVALUE ( Table1[ActualHours] ),
        0
    )

Rolling value =
SUMX (
    FILTER ( ALLSELECTED ( Table1), [ShiftDate] <= MAX ( PBI_MASTER_CALENDAR[Date] ) ),
    [Replaced Value]
)

YearMonth	Date	ActualHours	Replaced Value
Jan-17	1/01/2017	95.7	0
Jan-17	2/01/2017	95.9	0
Jan-17	3/01/2017	95.7	0
Jan-17	4/01/2017	93.7	0
Jan-17	5/01/2017	85.4	0
Jan-17	6/01/2017	95.5	0
Jan-17	7/01/2017	102.6	0
Jan-17	8/01/2017	88.2	0
Jan-17	9/01/2017	95.2	0

[Anonymous]

Looks like the result is what you gave, so thanks. However, I need to filter this data down a bit so when looking at granular level the measure applies.

Example, in the attached screenshot, I need the measure to calculate Planned Maintenance only (not a slicer).  For the yellow cells, I would expect the [Replaced Value] to be the same as ActualHours.

[v-shex-msft]

Hi @Anonymous,

If you can please share some sample data or pbix file for test and modify formula.
Notice: Please do mask on sensitive data before sharing.

Regards,

Xiaoxin Sheng

[Anonymous]

Hi, I have attached a sample file.

When looking at the data, Dragline 301 for Planned Maintenance only on 30/04, 01/05 and 03/05 should be 0 as the sum of these 3 days are >= 72 hours. Same with 01/05, 02/05 and 03/05. All other amounts for Dragline 301 for Planned Maintenance should be the same as ActualHours.

Also, you'll notice in the YearMonth field, the substotal is showing blank for [Replaced Value]. This needs to be the sum of all the values in [Replaced Value] for that month.

Thanks.

[Anonymous]

https://1drv.ms/u/s!ArxsIP-W81kFgppleAo0lyKh8f-hTQ

File is saved here, as I couldn't figure out how to attach a .pbix file

[Anonymous]

Basically, the outcome here is to ignore where there are 3 consecutive days of a summed total greater than 72 and sum the rest of the values at a Month-Year level with filters.