Calculate delta value of the same column based on dynamic filter

[SimonCOFRA]

Good day all,

I have a problem to figure out how to calculate new delta measure between 2 values (come from the same column) based on 2 different slicers. Dummy data example below:

Month ID       FCF       ReportingMonthA       ReportingMonthB
Jan                  10                 May                                May
Feb                 20                 May                                May
Jan                  50                  Jun                                 Jun

Feb                 100                 Jun                                 Jun
Jan                  200                 Jul                                  Jul

Feb                 500                  Jul                                 Jul

I would like to have 2 dynamic filters in visualization (ReportingMonthA and ReportingMonthB), based on what month I choose in those 2 dynamic filters, it would calculate the delta between the FCF value. I.E. If I set in ReportingMonthA a "May" and in ReportingMonthB a "Jul"; then I would like that the matrix table for MonthID in columns would show: 
MonthID "Jan" = 200 - 10 = 190
MonthID "Feb" = 500 - 20= 480.

The problem seems to be that once I set a ReportingMonthA filter on "Jul" and ReportingMonthB filter on "May" - the matrix does not return value from the table.

[Anonymous · Accepted solution]

Hi @SimonCOFRA ,

Based on your pbix, I have deleted all relationships you have built. And actually the [ReportingMonthB] column is not very necessary.

After doing these , please use the following formula to create measures

Month1 FCF =
CALCULATE (
    SUM ( 'FCF data'[FCF] ),
    FILTER (
        'FCF data',
        'FCF data'[Month ID] = MAX ( 'MonthID'[Month ID] )
            && 'FCF data'[ReportingMonthA] IN ALLSELECTED ( Month1[Month] )
    )
)

Month2 FCF =
CALCULATE (
    SUM ( 'FCF data'[FCF] ),
    FILTER (
        'FCF data',
        'FCF data'[Month ID] = MAX ( 'MonthID'[Month ID] )
            && 'FCF data'[ReportingMonthA] IN ALLSELECTED ( Month2[Month] )
    )
)

Calcalute the diff:

Diff = [Month2 FCF]-[Month1 FCF] 

The final output is shown below:

Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[SimonCOFRA]

Many thanks @Anonymous!!!

the solution provided works great! 

Kind regards,

Simonas

[amitchandak]

@SimonCOFRA , Create two independent table of months and the try

measure =

calculate(sum(Table[FCF]), filter(Table, Table[ReportingMonthA] in values(Month1[Month])))

-

calculate(sum(Table[FCF]), filter(Table, Table[ReportingMonthB] in values(Month2[Month])))

How to use two Date/Period slicers :https://www.youtube.com/watch?v=WSeZr_-MiTg

[SimonCOFRA]

Hi amitchandak,

many thanks for the idea. I tried to implement in the model but unfortunately it seems I still face the problem. It seems that filtering option still impacts the table, I cannot figure it out why. Just in case I attach the file - I would be eternally grateful if you could check where I do the misteps.

(link: https://drive.google.com/drive/folders/1UfAtsPg6dur4GxpVPFNNVf6QqsWR1pO7?usp=sharing)

Kind regards,
Simonas