Forum Discussion

mithunt's avatar
mithunt
Frequent Visitor
4 years ago
Solved

Help needed to create Calculated Column in finding Max\Min\Average for Last Month

Dear PowerBians

 

I would need some assistance in finding a solution on how to implement below using Calculated Column.

I was able to create using Calulate([Sales],PREVIOUSMONTH([Date])) however it gives as a value and I would like to use it as column for more calculations. Any help would be greatly appretiated. 

 

DateSalesMax of Last MonthMin of Last Month
1/1/2022110  
1/2/2022190  
1/3/2022138  
1/4/2022153  
1/5/2022136  
1/6/2022127  
1/7/2022138  
1/8/2022101  
1/9/2022192  
1/10/2022138  
1/11/2022104  
1/12/2022115  
1/13/2022148  
1/14/2022104  
1/15/2022184  
1/16/2022167  
1/17/2022144  
1/18/2022161  
1/19/2022154  
1/20/2022194  
1/21/2022159  
1/22/2022123  
1/23/2022173  
1/24/2022194  
1/25/2022108  
1/26/2022134  
1/27/2022135  
1/28/2022138  
1/29/2022169  
1/30/2022111  
1/31/2022177  
2/1/2022122194101
2/2/2022192194101
2/3/2022197194101
2/4/2022198194101
2/5/2022114194101
2/6/2022156194101
2/7/2022175194101
2/8/2022130194101
2/9/2022141194101
2/10/2022104194101
2/11/2022132194101
2/12/2022170194101
2/13/2022146194101
2/14/2022116194101
2/15/2022122194101
2/16/2022191194101
2/17/2022167194101
2/18/2022160194101
2/19/2022134194101
2/20/2022157194101
2/21/2022112194101
2/22/2022179194101
2/23/2022118194101
2/24/2022125194101
2/25/2022128194101
2/26/2022117194101
2/27/2022179194101
2/28/2022104194101
  • CNENFRNL's avatar
    CNENFRNL
    4 years ago

    I opt for non-context-transition func, typically date/time func instead of Time Intelligent funcs in a calculated column in order to avoid "undermining" intrinsic row context or unnecessary filter context altering.

    Min LM = 
VAR __eolm = EOMONTH( EDATE( DATA[Date], -1 ), 0 )
RETURN
    MINX( FILTER( DATA, EOMONTH( DATA[Date], 0 ) = __eolm ), DATA[Sales] )

     

    Excel worksheet formula is powerful enough,

  • Whitewater100's avatar
    Whitewater100
    4 years ago

    You are welcome! Would you be able to mark my reply as solved? Thanks!!

5 Replies

  • Whitewater100's avatar
    Whitewater100
    Icon for Solution Sage rankSolution Sage
    4 years ago

    Hi:

    You can first add a calc col for month.(could be a variable) If it's a mutli-year observation then add calc col for month-yr. I am just adding the month in so you can see.

    *I'm calling your table ,"Table"

    Month = MONTH('Table'[Date])
    Min of L Month =
    CALCULATE(MIN('Table'[Sales]),PREVIOUSMONTH('Table'[Date]),ALLEXCEPT('Table','Table'[Month]))
    Max of L Month =
    CALCULATE(Max('Table'[Sales]),PREVIOUSMONTH('Table'[Date]),ALLEXCEPT('Table','Table'[Month]))
    I hope this is what you need..

     

    • mithunt's avatar
      mithunt
      Frequent Visitor
      4 years ago

      Thank you very much Whitewater, I was able to create the colum as required. 

      • Whitewater100's avatar
        Whitewater100
        Icon for Solution Sage rankSolution Sage
        4 years ago

        You are welcome! Would you be able to mark my reply as solved? Thanks!!

  • CNENFRNL's avatar
    CNENFRNL
    Icon for Community Champion rankCommunity Champion
    4 years ago

    I opt for non-context-transition func, typically date/time func instead of Time Intelligent funcs in a calculated column in order to avoid "undermining" intrinsic row context or unnecessary filter context altering.

    Min LM = 
VAR __eolm = EOMONTH( EDATE( DATA[Date], -1 ), 0 )
RETURN
    MINX( FILTER( DATA, EOMONTH( DATA[Date], 0 ) = __eolm ), DATA[Sales] )

     

    Excel worksheet formula is powerful enough,