cancel
Showing results for
Did you mean:
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])

return

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.

4 REPLIES 4
Community Support

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.

Best Regards,
Community Support Team _ kalyj

Super User

Try removing the FILTER call

``````13period_ =
VAR P =
SELECTEDVALUE ( CCS[Year & Period Short] )
RETURN
CALCULATE (
[ccicount],
ALL ( CCS ),
CCS[Year & Period Short] >= P - 12
&& CCS[Year & Period Short] <= P
)
``````
New Member

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

Super User

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.

Announcements

#### 2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

#### Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

#### 2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture.

Top Solution Authors
Top Kudoed Authors