Skip to main content
Showing results for 
Search instead for 
Did you mean: 

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.

New Member

13 period Rolling data when selected values as period in slicer

Hi friends,


Need some quick help.

In Table A I have col - PERIOD LOOKUP

In Table B I have 2 cols - Year & Period Short and CCI_Num

I want to count the CCI_Num but cumulatively for last 13 months ( Here I have the date col in Whole number datatype and we should not change this to date datatype as per client)


When I select the PERIOD LOOKUP value in the slicer for example - 201901 the I should get the cumulative count of the CCI_Num for the last 13 months i.e upto - 201712 (Difference between 201901 and 201712 is 13 months)


I am getting the result but when I select the value in the slicer I am not getting the last 13 months in the visual.


I used the formula - 13period_ =

 var P = SELECTEDVALUE(CCS[Year & Period Short])


CALCULATE([ccicount],FILTER(ALL(CCS), CCS[Year & Period Short]>=P-12  && CCS[Year & Period Short]<=P))


I tried the disconnected table concept but unable to get the result.


Request to help.



Community Support
Community Support

Hi @kumar_powerbi ,

According to your description, you want to get the cumulative count of the CCI_Num for the last 13 months by the PERIOD LOOKUP value, but in your formula, your column referenced in the selectedvalue isn't PERIOD LOOKUP column.


I modify the formula and get this result.


Because the two tables are not related, all the date will get the same cumulative value based on the selected date in the slicer. 

I gusee this is not your expected result, but I'm not very clear what's your expected result. 

X-axis: display only the 13 previous month ?

Y-axis: aren't all values the same as my snapshot, if not, what's the result. For example, the slicer select 201901, for the date 201901, it should be cumulative from 201712 to 201901, then what's the calculation for 201812.

Could you please explain more about it?


Best Regards,
Community Support Team _ kalyj

Super User
Super User

Try removing the FILTER call

13period_ =
    SELECTEDVALUE ( CCS[Year & Period Short] )
        ALL ( CCS ),
        CCS[Year & Period Short] >= P - 12
            && CCS[Year & Period Short] <= P

If we remove filter ALL  it not calculating cumulative and its only cumulating only with in the year,
it will not considering next year..,
For Ex :  if i select 202203   the vlues showing past 13 months for cumulative start from slected values..,
its cumulating only 202113 for last 13 period  next starting 202212 not cumulating the value..
its considering only with in the period only..
I want like rolling cumulative of 13 period values as its start's from selected period values in slicer

The ALL is still included in my code, it is the call to the FILTER function which I suggest removing. You don't need that in parameters which you pass to CALCULATE.

Helpful resources

Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City


Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors