Forum Discussion

fbostan1's avatar
fbostan1
Helper I
1 year ago
Solved

DAX Formula

Hi,  I need help to figure how to subtract line 1 from lline 2 and so on.. is this needs to be done in a DAX formula or is there another way that can be done?     thanks
Need Help
  • Elena_Kalina's avatar
    Elena_Kalina
    1 year ago

    I hope I correctly understood what you are achieving. Here is a new measure

    Row Difference = 
VAR CurrentCountry = SELECTEDVALUE('Securities Cash Flow'[County])
VAR CurrentMonth = SELECTEDVALUE('Securities Cash Flow'[Month Name])
VAR CurrentYear = SELECTEDVALUE('Securities Cash Flow'[New Biz Year])
VAR CurrentAVCount = SELECTEDVALUE('Securities Cash Flow'[AY_Count])
VAR CurrentValue = SELECTEDVALUE('Securities Cash Flow'[Mutual Funds])

VAR PreviousValue = 
    CALCULATE(
        SELECTEDVALUE('Securities Cash Flow'[Mutual Funds]),
        FILTER(
            ALL('Securities Cash Flow'),
            'Securities Cash Flow'[County] = CurrentCountry &&
            'Securities Cash Flow'[Month Name] = CurrentMonth &&
            'Securities Cash Flow'[New Biz Year] = CurrentYear &&
            'Securities Cash Flow'[AY_Count] = CurrentAVCount - 1
        )
    )

RETURN
    IF(
        NOT ISBLANK(CurrentValue) && NOT ISBLANK(PreviousValue),
        CurrentValue - PreviousValue,
        BLANK()
    )

     

     

  • Elena_Kalina's avatar
    Elena_Kalina
    1 year ago

    Try this one

    Year to Year Difference = 
VAR CurrentCountry = SELECTEDVALUE('Securities Cash Flow'[County])
VAR CurrentMonth = SELECTEDVALUE('Securities Cash Flow'[Month Name])
VAR CurrentYear = SELECTEDVALUE('Securities Cash Flow'[New Biz Year])
VAR CurrentAVCount = SELECTEDVALUE('Securities Cash Flow'[AY_Count])
VAR CurrentValue = SELECTEDVALUE('Securities Cash Flow'[Mutual Funds])

VAR PreviousYearValue = 
    CALCULATE(
        SELECTEDVALUE('Securities Cash Flow'[Mutual Funds]),
        FILTER(
            ALL('Securities Cash Flow'),
            'Securities Cash Flow'[County] = CurrentCountry &&
            'Securities Cash Flow'[Month Name] = CurrentMonth &&
            'Securities Cash Flow'[New Biz Year] = CurrentYear - 1 &&
            'Securities Cash Flow'[AY_Count] = CurrentAVCount
        )
    )

RETURN
    IF(
        NOT ISBLANK(CurrentValue) && NOT ISBLANK(PreviousYearValue),
        CurrentValue - PreviousYearValue,
        BLANK()
    )
Elena_Kalina's avatar
Elena_Kalina
Solution Sage
1 year ago

Hi fbostan1 

Please try to use this measure

Row Difference = 
VAR CurrentRowValue = SELECTEDVALUE('Securities Cash Flow'[Mutual Funds], BLANK())
VAR CurrentIndex = SELECTEDVALUE('Securities Cash Flow'[AY_COUNT])
VAR PreviousRowValue = 
    LOOKUPVALUE(
        'Securities Cash Flow'[Mutual Funds],
        'Securities Cash Flow'[AY_COUNT], CurrentIndex - 1,
        'Securities Cash Flow'[Month Name], SELECTEDVALUE('Securities Cash Flow'[Month Name])
    )
RETURN
    IF(
        NOT ISBLANK(CurrentRowValue) && NOT ISBLANK(PreviousRowValue),
        CurrentRowValue - PreviousRowValue,
        BLANK()
    )

 

If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos" – I’d truly appreciate it! 

Thank you.

 

  • fbostan1's avatar
    fbostan1
    Helper I
    1 year ago

    this formula not working for me, 

    sorry I forgot to mention that those numbers was added from US anc CAN to get North America. 

     

    • Elena_Kalina's avatar
      Elena_Kalina
      Solution Sage
      1 year ago

      I hope I correctly understood what you are achieving. Here is a new measure

      Row Difference = 
VAR CurrentCountry = SELECTEDVALUE('Securities Cash Flow'[County])
VAR CurrentMonth = SELECTEDVALUE('Securities Cash Flow'[Month Name])
VAR CurrentYear = SELECTEDVALUE('Securities Cash Flow'[New Biz Year])
VAR CurrentAVCount = SELECTEDVALUE('Securities Cash Flow'[AY_Count])
VAR CurrentValue = SELECTEDVALUE('Securities Cash Flow'[Mutual Funds])

VAR PreviousValue = 
    CALCULATE(
        SELECTEDVALUE('Securities Cash Flow'[Mutual Funds]),
        FILTER(
            ALL('Securities Cash Flow'),
            'Securities Cash Flow'[County] = CurrentCountry &&
            'Securities Cash Flow'[Month Name] = CurrentMonth &&
            'Securities Cash Flow'[New Biz Year] = CurrentYear &&
            'Securities Cash Flow'[AY_Count] = CurrentAVCount - 1
        )
    )

RETURN
    IF(
        NOT ISBLANK(CurrentValue) && NOT ISBLANK(PreviousValue),
        CurrentValue - PreviousValue,
        BLANK()
    )