rolling measure with period formats yyyymm and continuous month numbers

[iiilillilil]

im working with the period rolling calculations where the model needs to show the rolling measures of the past 13 periods upon selection of a single period and also needs to visulaize the data on a chart by just showing the past 13 periods and 26 periods in different charts. The major problem im facing is that right after selecting the period on a slicer the table gives the rolling measure back but it takes the first value as number 1 instead that needs to be a rolling measure too. another problem i face in here is since the periods are in the format of yyyymm. for example the period values are in the format like 202201-202213 and the next value starts like 202301. for this reason based on the dax i created it recognises the value of the period as a number and calculates as 202301-13 instead that needs to be the value of 202201. 

1. CC_13periodrolling =

   var currentrollingnumber = SELECTEDVALUE('CloseCalls'[Year & Period Short])

   return CALCULATE([CCinNumber],FILTER(ALL('CloseCalls'),'CloseCalls'[Year & Period Short]>= currentrollingnumber-12 && 'CloseCalls'[Year & Period Short]<= currentrollingnumber))

   
   as you see in this picture the period value 202301 is not a rolling measure also the first value of the table is also not a rolling measure which clears that the period values are not considering the previous values and performing a rolling on them.  

 Thanks for your time and help!

[Anonymous]

Hi @iiilillilil ,

I'm a little confused about your needs, Could you please explain them further? It would be good to provide a screenshot of the results you are expecting.

Thanks for your efforts & time in advance.

Best regards,
Community Support Team_ Binbin Yu

[iiilillilil]

Hello @Anonymous thanks for the reply. I'm the above mentioned problem I'm trying to create the rolling measures of the past 13 periods of the CCinumber column. To be clear the column next to it CC-13periodrolling column should return the values summing up the past 13 periods. But using the dax I created the 202207 period 13 period measure is same as CCinnumber instead that needs to calculate the previous 13 values and return the sum of them. And, if you have a look at 202301 the value in the CC-13periodrolling is also same as CCinnumber which needs to be the rolling measure too. for example the previous values in CC-13periodrolling is 18947 and the period 202301 CCin number value is 2810 so the CC-13periodrolling should return back the sum of 18947 and 2810. The same applies to the 202207 period. hope this explanation made you clear 

[Anonymous]

Hi @iiilillilil ,

I create a table to test,please try below steps:

1. below is my test table

Table:

2. create a measure and add it to table visual

CC_13periodrolling =
VAR cur_yps =
    SELECTEDVALUE ( 'Table'[Year&Period Short] )
VAR tmp =
    FILTER (
        ALL ( 'Table' ),
        'Table'[Year&Period Short] >= cur_yps - 12
            && 'Table'[Year&Period Short] <= cur_yps
    )
RETURN
    SUMX ( tmp, [CCInnumber] )

Please refer the attached .pbix file.

If I have misunderstood your request, please feel free to let me know.

Best regards,
Community Support Team_ Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[iiilillilil]

firstly, thank you for your time @Anonymous . but following the dax you created also gives back the same results. the aim is to create the rolling sum taking the previous values for all the periods. please refer to the image mentioned below,

 

[Anonymous]

Hi @iiilillilil ,

According to your descriptions, my measure's calculate logic is "sum the [CCInnumber] for the past 12 [Year&Period Short] " .

Example: if  curent [Year&Period Short]  is 202212, should sum the [CCInnumber] between 202212 and 202201.

Could please provide your calculate logic?

Best regards,
Community Support Team_ Binbin Yu