Forum Discussion

RMarston's avatar
RMarston
Frequent Visitor
2 years ago

Check if column has same value for all dates

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...
Need Help
RMarston's avatar
RMarston
Frequent Visitor
2 years ago

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! ðŸ™‚