Forum Discussion

aaande8's avatar
aaande8
Frequent Visitor
6 years ago
Solved

How to remove current month reporting in Power Pivot

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 ha...
Need Help
  • v-juanli-msft's avatar
    v-juanli-msft
    6 years ago

    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's avatar
v-juanli-msft
Community Support
6 years ago

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.