Forum Discussion

UKNSI-Powerbi's avatar
UKNSI-Powerbi
Frequent Visitor
3 years ago
Solved

Row level variance

At the moment I have a table of customer cumulations grouped by  Cust/Seq/Contract/Part/Date/Release/YYYYMM   Cust Seq Contract Part Date Release Rev YYYYMM CUMULATION CUST1     1 0...
Need Help
  • FreemanZ's avatar
    FreemanZ
    3 years ago

    hi UKNSI-Powerbi 

    you may try to add a calculated column like this:

    Variance2 = 
VAR _date = [Date]
VAR _cumpre =
CALCULATE(
    SUM(TableName[CUMULATION]),    
    ALLEXCEPT(TableName, TableName[Cust], TableName[Seq], TableName[Contract], TableName[Part], TableName[YYYYMM]),
    TableName[Date] = _date-1
)
RETURN
DIVIDE( [CUMULATION] - _cumpre, _cumpre)

     

    tried and it worked like this:

amitchandak's avatar
amitchandak
Super User
3 years ago

UKNSI-Powerbi , Create a date table join with your date

 

and have measure like

This Day = CALCULATE(sum('Table'[Qty]))

 

This Day = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Date]=max('Date'[Date])))


Last Day = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Date]=max('Date'[Date])-1))


Last Day = CALCULATE(sum('Table'[Qty]), previousday('Date'[Date]))

 

and take a diff

Day Intelligence - Last day, last non continous day
https://medium.com/@amitchandak.1978/power-bi-day-intelligence-questions-time-intelligence-5-5-5c3243d1f9