Forum Discussion

mcornfield's avatar
mcornfield
Icon for Helper III rankHelper III
4 years ago

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

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

 

 

 

Need Help
Tips and Tricks

2 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    4 years ago

    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))

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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