Check if column has same value for all dates

[RMarston]

I have a date table with a calculated column called ShipMoClosed. There are only three values in this column: Closed, In Month, Future. Basically there is a data date in another table and if the date in the date table is before this data date it's "Closed", if it's in the same month it's "In Month", and if it's after then it's "Future". So at a single date level there will be only one value possible, but at a month level you could have a combo of Closed and In Month, then at a year level you could have all three.

Ultimately I want to use this to say if the month is closed, then just sum the shipments for the month/day/year. But if there is a combo of Closed and anything else then I want to run a different calculation: 

    IF (

        COUNTROWS (

            FILTER (

                CALENDAR ( STARTOFMONTH ( 'Date'[Date] ), ENDOFMONTH ( 'Date'[Date] ) ),

                WEEKDAY ( [Date], 2 ) < 6

                    && [Date]

                        <= MAX ( ShipmentsDataThru[Shipment Through] ) - 1

            )

        ) > 7,

        ROUND (

            (

                SUM ( [Case Shipments 9L] )

                    / COUNTROWS (

                        FILTER (

                            CALENDAR ( STARTOFMONTH ( 'Date'[Date] ), ENDOFMONTH ( 'Date'[Date] ) ),

                            WEEKDAY ( [Date], 2 ) < 6

                                && [Date]

                                    <= MAX ( ShipmentsDataThru[Shipment Through] ) - 1

                        )

                    )

            )

                * COUNTROWS (

                    FILTER (

                        CALENDAR ( STARTOFMONTH ( 'Date'[Date] ), ENDOFMONTH ( 'Date'[Date] ) ),

                        WEEKDAY ( [Date], 2 ) < 6

                    )

                ),

            0

        )

    )

This calculation works just fine on it's own in a matrix without checking if the month is closed or not, but I want to use this same measure for a rolling 3mo sum. The problem is that when you use the above in a R3 calculation it takes into account the full 90 day period, but what I want it to do is sum the actual sales for the previous two Closed months, and only use the above calculation for the current month, then sum all three values.

Here is the R3 DAX I'm using:

IF (

    NOT ( ISBLANK ( SUM ( 'Shipment Dashboard'[Forecast & History] ) ) )

        && COUNTROWS (

            FILTER (

                CALENDAR ( STARTOFMONTH ( 'Date'[Date] ), ENDOFMONTH ( 'Date'[Date] ) ),

                WEEKDAY ( [Date], 2 ) < 6

                    && [Date]

                        <= MAX ( ShipmentsDataThru[Shipment Through] ) - 1

            )

        ) >= 10

        && MIN ( 'Date'[Date] )

            >= EDATE (

                CALCULATE (

                    MIN ( 'Shipment Dashboard'[First Shipment] ),

                    ALLEXCEPT ( 'Date', 'Date'[Date] )

                ),

                2

            ),

    CALCULATE (

        [Measure Shipment Run Rate],

        DATESINPERIOD ( 'Date'[Date], EOMONTH ( MAX ( 'Date'[Date] ), 0 ), -3, MONTH )

    )

)

[RMarston]

I have gotten most of the way there in getting the results I was hoping for with this:

Measure Shipment Run Rate =

 IF (
    COUNTROWS (
        FILTER (
            CALENDAR ( STARTOFMONTH ( 'Date'[Date] ), ENDOFMONTH ( 'Date'[Date] ) ),
            WEEKDAY ( [Date], 2 ) < 6
                && [Date]
                    <= MAX ( ShipmentsDataThru[Shipment Through] ) - 1
        )
    ) > 7,
    ROUND (
        (
            SUM ( [Case Shipments 9L] )
                / COUNTROWS (
                    FILTER (
                        CALENDAR ( STARTOFMONTH ( 'Date'[Date] ), ENDOFMONTH ( 'Date'[Date] ) ),
                        WEEKDAY ( [Date], 2 ) < 6
                            && [Date]
                                <= MAX ( ShipmentsDataThru[Shipment Through] ) - 1
                    )
                )
        )
            * COUNTROWS (
                FILTER (
                    CALENDAR ( STARTOFMONTH ( 'Date'[Date] ), ENDOFMONTH ( 'Date'[Date] ) ),
                    WEEKDAY ( [Date], 2 ) < 6
                )
            ),
        0
    )
)

And for the Rolling 3mo calculation:

Measure R3 Ships=

    IF (
        NOT ( ISBLANK ( SUM ( 'Shipment Dashboard'[Forecast & History] ) ) )
            && COUNTROWS (
                FILTER (
                    CALENDAR ( STARTOFMONTH ( 'Date'[Date] ), ENDOFMONTH ( 'Date'[Date] ) ),
                    WEEKDAY ( [Date], 2 ) < 6
                        && [Date]
                            <= MAX ( ShipmentsDataThru[Shipment Through] ) - 1
                )
            ) >= 10
            && MIN ( 'Date'[Date] )
                >= EDATE (
                    CALCULATE (
                        MIN ( 'Shipment Dashboard'[First Shipment] ),
                        ALLEXCEPT ( 'Date', 'Date'[Date] )
                    ),
                    2
                ),
        CALCULATE (
            SUM ( 'Shipment Dashboard'[Case Shipments 9L] ),
            DATESINPERIOD ( 'Date'[Date], EOMONTH ( MAX ( 'Date'[Date] ), 0 ), -3, MONTH )
        )
            - SUM ( 'Shipment Dashboard'[Case Shipments 9L] ) + [Measure Shipment Run Rate]
    )

This produces the desired result as long as I am only showing details by month:

What I don't understand is when I use @rajendraongole1 R3 caluclation, on the year level it says 0 for the current year but has the Dec R3 for every other year, and with mine it comes up with a number I can't even figure out how it calculated.

I would really like to get something that shows the latest value for the R3 of the year on the year level, so for the picture above it would say 21,070. Thanks again for all the help! 🙂

[rajendraongole1]

Hi @RMarston create a measure to sum the shipments for the closed months, current month and rolling calculation as follows ,use the rolling r3 measure in your visualization.

Measure_ClosedShipments =
CALCULATE(
SUM([Case Shipments 9L]),
FILTER(
'Date',
'Date'[ShipMoClosed] = "Closed"
)
)

then create another measure for calculation when the month is not fully closed 

Measure_CurrentMonthCalculation =
IF (
COUNTROWS (
FILTER (
CALENDAR ( STARTOFMONTH ( 'Date'[Date] ), ENDOFMONTH ( 'Date'[Date] ) ),
WEEKDAY ( [Date], 2 ) < 6
&& [Date]
<= MAX ( ShipmentsDataThru[Shipment Through] ) - 1
)
) > 7,
ROUND (
(
SUM ( [Case Shipments 9L] )
/ COUNTROWS (
FILTER (
CALENDAR ( STARTOFMONTH ( 'Date'[Date] ), ENDOFMONTH ( 'Date'[Date] ) ),
WEEKDAY ( [Date], 2 ) < 6
&& [Date]
<= MAX ( ShipmentsDataThru[Shipment Through] ) - 1
)
)
)
* COUNTROWS (
FILTER (
CALENDAR ( STARTOFMONTH ( 'Date'[Date] ), ENDOFMONTH ( 'Date'[Date] ) ),
WEEKDAY ( [Date], 2 ) < 6
)
),
0
),
SUM([Case Shipments 9L])
)

Create final measure for rolling 3 months sum calculation

Measure_R3 =
VAR CurrentMonth = MAX('Date'[Date])
VAR TwoMonthsAgo = EOMONTH(CurrentMonth, -2)

VAR ClosedMonthsSum =
CALCULATE(
[Measure_ClosedShipments],
DATESBETWEEN('Date'[Date], TwoMonthsAgo, EOMONTH(CurrentMonth, -1))
)

VAR CurrentMonthSum =
CALCULATE(
[Measure_CurrentMonthCalculation],
FILTER(
'Date',
'Date'[ShipMoClosed] <> "Closed"
&& MONTH('Date'[Date]) = MONTH(CurrentMonth)
&& YEAR('Date'[Date]) = YEAR(CurrentMonth)
)
)

RETURN
ClosedMonthsSum + CurrentMonthSum

use the above R3 measure in your matrix visual, this correctly sums the actual and previous closed months values.

Check and let know.

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!

[RMarston]

This does not result in the correct calculation it shows only the rolling total with the previous two months closed and does not include the Run Rate Month. I think this is because we are saying <>"Closed" but in reality there are two values in the current month: Closed and In Month