How to remove current month reporting in Power Pivot

[aaande8]

I have a report that I want to pull in a rolling 12 months. However, I also need it to not pull in  the data for the current month as month end reporting happens halfway into the current month.  I have the rolling 12 month slicer. I have the group by month number. I looked into the filter for the column that pulls in the record date to filter anything prior to current month. However, it only allows for a specific date. Has anyone worked with month end reports where they needed to not include the current months data in their pivot charts? Whats the best way to solve this solution? Also my pivot chart seems to want to pull in September from this year and last year. Basically I need a rolling 12 month of Sept 2018  to August 2019. That way the pivot table doesnt pull in Sept 2019

[v-juanli-msft]

Hi @aaande8 

If there is no slicer to filter the line chart, try measures below

Measure =
VAR currentday =
    MAX ( 'calendar'[Date] )
RETURN
    CALCULATE (
        SUM ( 'Table'[sales] ),
        FILTER (
            ALLSELECTED ( 'calendar' ),
            DATEDIFF ( currentday, 'calendar'[Date], MONTH ) >= -11
                && DATEDIFF ( currentday, 'calendar'[Date], MONTH ) <= 0
                && 'calendar'[Date] <= MAX ( 'calendar'[Date] )
        )
    )

Measure 5 = IF(DATEDIFF(MAX('calendar'[Date]),TODAY(),MONTH)>0,[Measure])

Best Regards
Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-juanli-msft · Accepted solution]

Hi @aaande8 

My previous answer show specific rolling 12 months values:

for current month, 2019/9, the rolling 12 month values is from 2018/10~2019/8, not including current month.

If you'd like there is no value showing at X-axis =2019/9 , 

You could create a measure as below

Measure =
VAR currentday =
    MAX ( 'calendar'[Date] )
RETURN
    CALCULATE (
        SUM ( 'Table'[sales] ),
        FILTER (
            ALLSELECTED ( 'calendar' ),
            DATEDIFF ( currentday, 'calendar'[Date], MONTH ) >= -11
                && DATEDIFF ( currentday, 'calendar'[Date], MONTH ) <= 0
                && 'calendar'[Date] <= MAX ( 'calendar'[Date] )
        )
    )

Best Regards
Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-juanli-msft]

Hi @aaande8 

Based on my understanding, what you expected is as below

year	month	rolling sum 12 month
2019	7	sum of (2018/8~2019/7)
2019	8	sum of (2018/9~2019/8)
2019	9	sum of (2018/10~2019/8)

If so, you could refer to this thread.

For your scenario, i modify the formula as below

Create a table

calendar = ADDCOLUMNS(CALENDARAUTO(),"year",YEAR([Date]),"month",MONTH([Date]))

Create columns in this table

start of month = STARTOFMONTH('calendar'[Date])

end of month = ENDOFMONTH('calendar'[Date])

Create measures

Measure 2 =
IF (
    MAX ( 'Table'[sales] ) <> BLANK (),
    IF (
        TODAY () < MAX ( 'calendar'[end of month] ),
        CALCULATE (
            SUM ( 'Table'[sales] ),
            DATESBETWEEN (
                'calendar'[Date],
                NEXTDAY ( SAMEPERIODLASTYEAR ( 'calendar'[Date] ) ),
                PREVIOUSMONTH ( 'calendar'[end of month] )
            )
        ),
        CALCULATE (
            SUM ( 'Table'[sales] ),
            DATESBETWEEN (
                'calendar'[Date],
                NEXTDAY ( SAMEPERIODLASTYEAR ( 'calendar'[Date] ) ),
                LASTDATE ( 'calendar'[Date] )
            )
        )
    )
)

Best Regards
Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.