Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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] ) ) ) ) )
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
102 | |
53 | |
21 | |
13 | |
11 |