cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
kumar_powerbi
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.

d1484537-ad31-4086-b38d-37e1871cb9b9.png

 

4 REPLIES 4
v-kalyj-msft
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.

vkalyjmsft_0-1664440534514.png

I modify the formula and get this result.

vkalyjmsft_1-1664440808643.png

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

johnt75
Super User
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
    )

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

Announcements
Carousel_PBI_Wave1

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.

Power BI Summit Carousel 2

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!

Thank you 2022 Review

2022 Monthly Feature Releases

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