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

7 Replies

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

         

         

  • fbostan1's avatar
    fbostan1
    Helper I
    1 year ago

    Hi Elena_Kalina ,

     

    I am expending this dashboard. Would it be possible to tweek this Dax to get me a year to year difference on a new table OR I need a different Dax formula?

    • Elena_Kalina's avatar
      Elena_Kalina
      Solution Sage
      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()
    )