Counting number of records for last 6 periods excluding current period

[DianaChris]

Hi, I'm new to PowerBI and I've a requirement where I need to count number of records for 6 periods excluding current period. (1 period=4weeks) likewise for a financial year(April-March) we have 13 periods.

For example: if my current period is 4 then I need to exclude the number of records falling under dates of this current period and count 6 period backwards (ie. Period- {3,2,1,13,12,11}). In my table I've both date and period column. 

Can some one pls help me with the dax to solve this?

[DianaChris]

Thank you so much for the solution!

[v-stephen-msft · Accepted solution]

Hi @DianaChris ,

Here's my solution.

1.You could create a Period2 column which it is no cycle 1-13, but from 1 to calculate down.

Period2 = 
var _rank=RANKX('Calendar',[Date],,ASC,Dense)
return ROUNDUP(DIVIDE(_rank,28),0)

2.Then you can count number of records for last 6 periods excluding current period.

Measure = var _current=CALCULATE(MAX('Calendar'[Period2]),FILTER(ALL('Calendar'),[Date]=TODAY()))
return CALCULATE(COUNTROWS('Table'),FILTER(ALL('Calendar'),[Period2]>=_current-6&&[Period2]<_current))

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.