Calculate SUM on previous month, but previous month changes based on day of month

[aellison]

I'm trying to figure out how to compare 2 months totals based on a date range. The date range required changes on the 15th of the month. Also, this needs to be a measure. I am trying to help someone with an issue where the data source is a tabular model, so creating columns is not an option without changing the cube (which requires change control and a longer time to wait)

Example:

     If the report was run on from April 1st - 14th, then the sum comparison would be for Feb vs March.

     If the report was run on April 15th throug the end of the month , then the sum comparison would be for March vs April.

    Any prevous months would use it's own previous month (or parellelperiod) to compare. Only the current month's comparison changes based on the day of month.

Thanks in advance,

[v-yulgu-msft · Accepted solution]

Hi @aellison ,

Please refer to below measures:

Current Month = 
IF (
    DAY ( TODAY () ) <= 14,
    IF (
        MONTH ( TODAY () ) <> 1,
        CALCULATE (
            SUM ( Sample1[Sales] ),
            FILTER (
                ALLSELECTED ( Sample1 ),
                YEAR ( Sample1[Date] ) = YEAR ( TODAY () )
                    && MONTH ( Sample1[Date] )
                        = MONTH ( TODAY () ) - 1
            )
        ),
        CALCULATE (
            SUM ( Sample1[Sales] ),
            FILTER (
                ALLSELECTED ( Sample1 ),
                YEAR ( Sample1[Date] )
                    = YEAR ( TODAY () ) - 1
                    && MONTH ( Sample1[Date] ) = 12
            )
        )
    ),
    CALCULATE (
        SUM ( Sample1[Sales] ),
        FILTER (
            ALLSELECTED ( Sample1 ),
            YEAR ( Sample1[Date] ) = YEAR ( TODAY () )
                && MONTH ( Sample1[Date] ) = MONTH ( TODAY () )
        )
    )
)

Previous Month = 
IF (
    DAY ( TODAY () ) <= 14,
    IF (
        MONTH ( TODAY () ) = 1,
        CALCULATE (
            SUM ( Sample1[Sales] ),
            FILTER (
                ALLSELECTED ( Sample1 ),
                YEAR ( Sample1[Date] )
                    = YEAR ( TODAY () ) - 1
                    && MONTH ( Sample1[Date] ) = 11
            )
        ),
        IF (
            MONTH ( TODAY () ) = 2,
            CALCULATE (
                SUM ( Sample1[Sales] ),
                FILTER (
                    ALLSELECTED ( Sample1 ),
                    YEAR ( Sample1[Date] )
                        = YEAR ( TODAY () ) - 1
                        && MONTH ( Sample1[Date] ) = 12
                )
            ),
            CALCULATE (
                SUM ( Sample1[Sales] ),
                FILTER (
                    ALLSELECTED ( Sample1 ),
                    YEAR ( Sample1[Date] ) = YEAR ( TODAY () )
                        && MONTH ( Sample1[Date] )
                            = MONTH ( TODAY () ) - 2
                )
            )
        )
    ),
    IF (
        MONTH ( TODAY () ) <> 1,
        CALCULATE (
            SUM ( Sample1[Sales] ),
            FILTER (
                ALLSELECTED ( Sample1 ),
                YEAR ( Sample1[Date] ) = YEAR ( TODAY () )
                    && MONTH ( Sample1[Date] )
                        = MONTH ( TODAY () ) - 1
            )
        ),
        CALCULATE (
            SUM ( Sample1[Sales] ),
            FILTER (
                ALLSELECTED ( Sample1 ),
                YEAR ( Sample1[Date] )
                    = YEAR ( TODAY () ) - 1
                    && MONTH ( Sample1[Date] ) = 12
            )
        )
    )
)

Curr VS Prev = [Current Month]-[Previous Month]

Best regards,

Yuliana Gu