The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredCompete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.
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
Solved! Go to 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] ) ) )
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])
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] ) ) )
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] ) ) ) ) )