DAX Formula

[fbostan1]

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

[Elena_Kalina · Accepted solution]

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]

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 · Accepted solution]

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]

@Elena_Kalina Thank you so much, this one worked perfectly fine! Kudos to you!!

[Jihwan_Kim]

Hi,

One of ways is using visual calculation.
Please check the below picture and the attached pbix file, and I tried to create a sample pbix file like below.

Using visual calculations in Power BI Desktop - Power BI | Microsoft Learn

[Elena_Kalina]

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]

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 · Accepted solution]

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