DAX or M Query Help How to Calculate previous or last Cycle

[mcornfield]

I have a Table Showing [#Achieved]

We look at it by cycle which are 2 month Incements 

Jan- Feb

Mar-Apr

May-Jun 

Etc.

I need to figure out how to compare against the previous cycle The formula I have so far works till I break the data out by a different Dimension. See image below

Here is what I am going for:

Link to PBIX

https://drive.google.com/file/d/167jGIQ9KoBXLgVjNOPAaXM21nn_mMUxV/view?usp=sharing

[Anonymous]

Hi @mcornfield ,

You are using ALL() function to wrap the table, so the result won't be filtered by dimensions.

Try using "ALLEXCEPT('Table','Table'[Dimention ])" instead.

Best Regards,

Jay

[amitchandak]

@mcornfield ,

New columns in date table
Period Name = if( mod(month([Date]),2) =0, format(eomonth([Date],-1),"mmm" ) & "-" &format([date], "mmm") , format([date], "mmm") & "-" & format(eomonth([Date],1),"mmm" ) )

Period Year = Year([Date])*100 + Quotient(month([Date])+1,2)

Period Rank = RANKX(all(Period),Period[year period],,ASC,Dense)

New measures

This Period = CALCULATE(sum('Table'[Qty]), FILTER(ALL(Period),Period[Period Rank]=max(Period[Period Rank])))
Last Period = CALCULATE(sum('Table'[Qty]), FILTER(ALL(Period),Period[Period Rank]=max(Period[Period Rank])-1))