cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
aaande8 Frequent Visitor
Frequent Visitor

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

 

Capture2.PNGCapture3.PNGCapture4.PNG

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: How to remove current month reporting in Power Pivot

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

Capture16.JPG

 

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.

View solution in original post

3 REPLIES 3
Community Support Team
Community Support Team

Re: How to remove current month reporting in Power Pivot

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

Capture1.JPG

 

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.

 

Community Support Team
Community Support Team

Re: How to remove current month reporting in Power Pivot

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

Capture16.JPG

 

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.

View solution in original post

Community Support Team
Community Support Team

Re: How to remove current month reporting in Power Pivot

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])
Capture17.JPG
 
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.

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 312 members 3,204 guests
Please welcome our newest community members: