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