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 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

 

 

 

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.

3 Replies

  • 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.

     

  • v-juanli-msft's avatar
    v-juanli-msft
    Community Support
    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 
    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.