Rolling sum with a single month selected in slicer

[deasons22]

I'm looking to get a 12 month Sum based on a single month selection in  a slicer. I have a formula that is totaling correctly, but only when i select at least a year+ worth of months. Ideally this will default to opening in the current month, so i would like to get a total of the 12 months prior.

Thanks,

Sam

[v-jiascu-msft]

Hi @deasons22,

Could you please mark the proper answers as solutions?

Best Regards,

Dale

[shebr]

Hi @deasons22

Could you share your code for the measure you have created, or perhaps the pbix file? It could be that your measure needs some additional functions adding to it such as the ALL function. This allows tables/columns to be ignored when filtering.

Thanks

shebr

[deasons22]

Hi Shebr,

Here's what i'm currently using. 

Current Customer Growth Count rolling average =
IF(
ISFILTERED('Dim_Date'[PK_Date]),
ERROR("Time intelligence quick measures can only be grouped or filtered by the Power BI-provided date hierarchy or primary date column."),
VAR __LAST_DATE = ENDOFMONTH('Dim_Date'[PK_Date].[Date])
VAR __DATE_PERIOD =
DATESBETWEEN(
'Dim_Date'[PK_Date].[Date],
STARTOFMONTH(DATEADD(__LAST_DATE, -12, MONTH)),
__LAST_DATE
)
RETURN
SUMX(
CALCULATETABLE(
SUMMARIZE(
VALUES('Dim_Date'),
'Dim_Date'[PK_Date].[Year],
'Dim_Date'[PK_Date].[QuarterNo],
'Dim_Date'[PK_Date].[Quarter],
'Dim_Date'[PK_Date].[MonthNo],
'Dim_Date'[PK_Date].[Month]
),
__DATE_PERIOD
),
CALCULATE([Current Customer Growth Count], ALL('Dim_Date'[PK_Date].[Day]))
)
)

[v-jiascu-msft · Accepted solution]

Hi @deasons22,

Since you said the formula works fine, I would suggest you change the blue part like below.

Current Customer Growth Count rolling average =
IF (
    ISFILTERED ( 'Dim_Date'[PK_Date] ),
    ERROR ( "Time intelligence quick measures can only be grouped or filtered by the Power BI-provided date hierarchy or primary date column." ),
    VAR __LAST_DATE =
        IF (
            ISBLANK ( SELECTEDVALUE ( 'Dim_Date'[PK_Date] ) ),
            ENDOFMONTH ( 'Dim_Date'[PK_Date].[Date] ),
            EOMONTH ( SELECTEDVALUE ( 'Dim_Date'[PK_Date] ), 0 )
        )
    VAR __DATE_PERIOD =
        DATESBETWEEN (
            'Dim_Date'[PK_Date].[Date],
            STARTOFMONTH ( DATEADD ( __LAST_DATE, -12, MONTH ) ),
            __LAST_DATE
        )
    RETURN
        SUMX (
            CALCULATETABLE (
                SUMMARIZE (
                    VALUES ( 'Dim_Date' ),
                    'Dim_Date'[PK_Date].[Year],
                    'Dim_Date'[PK_Date].[QuarterNo],
                    'Dim_Date'[PK_Date].[Quarter],
                    'Dim_Date'[PK_Date].[MonthNo],
                    'Dim_Date'[PK_Date].[Month]
                ),
                __DATE_PERIOD
            ),
            CALCULATE ( [Current Customer Growth Count], ALL ( 'Dim_Date'[PK_Date].[Day] ) )
        )
)

Best Regards,

Dale

[deasons22]

Thanks I'll give that a shot.