Forum Discussion

Haja007's avatar
Haja007
Regular Visitor
2 years ago
Solved

calculated column

Hello everyone, I'm a beginner in power bi and I don't know if my problem is because of my model or something else. In short, I have a table containing lists of products with several columns includi...
  • talespin's avatar
    talespin
    2 years ago
    Hi Haja007 
     
    Assuming that date will always be monthend date. If not please modify the date condition accordingly.
    (EOMONTH( _InvDate, -3) + 1) && QtyTbl[Inventory date] <= _InvDate
    Above formula first subtracts 3 months from current date and return end of month and +1 to give beginning of next month.
     
    New Col =
    VAR _Product = QtyTbl[Products]
    VAR _Store = QtyTbl[store]
    VAR _InvDate = QtyTbl[Inventory date]
    VAR avgQtyLast3Mths =
                        CALCULATE(
                                    AVERAGE(QtyTbl[qtCons]),
                                    REMOVEFILTERS(QtyTbl),
                                    QtyTbl[Products] = _Product,
                                    QtyTbl[store] = _Store,
                                    QtyTbl[Inventory date] >= (EOMONTH( _InvDate, -3) + 1) && QtyTbl[Inventory date] <= _InvDate
                        )
    RETURN DIVIDE( QtyTbl[EOMStock ], avgQtyLast3Mths)
     

     


     

talespin's avatar
talespin
Solution Sage
2 years ago
Hi Haja007 
 
Assuming that date will always be monthend date. If not please modify the date condition accordingly.
(EOMONTH( _InvDate, -3) + 1) && QtyTbl[Inventory date] <= _InvDate
Above formula first subtracts 3 months from current date and return end of month and +1 to give beginning of next month.
 
New Col =
VAR _Product = QtyTbl[Products]
VAR _Store = QtyTbl[store]
VAR _InvDate = QtyTbl[Inventory date]
VAR avgQtyLast3Mths =
                    CALCULATE(
                                AVERAGE(QtyTbl[qtCons]),
                                REMOVEFILTERS(QtyTbl),
                                QtyTbl[Products] = _Product,
                                QtyTbl[store] = _Store,
                                QtyTbl[Inventory date] >= (EOMONTH( _InvDate, -3) + 1) && QtyTbl[Inventory date] <= _InvDate
                    )
RETURN DIVIDE( QtyTbl[EOMStock ], avgQtyLast3Mths)