Cumulative sum to-date minus cumulative sum up to last moth, the subtotal not correct

[vic_cin]

Hi all, 

Would like to get help in the following DAX measures. I would like to have the measure for number 3 to be able to showed as per the 4th column. 

1. I have created a sales GDV cumulative
2 Then created a sales GDV cumulative_last mth
3. When (1) - (2) = test_GDV_CM-LM  , I cannot get the results that I would like to have, as per the 4th column below. 

Measures

1. 

Sales GDV_Cumulative =

VAR _maxDate = MAX('Fact_Direct Cost POC reallocate'[Construction period])

VAR _ver = SELECTEDVALUE('Version'[Version])

RETURN

    CALCULATE(

        [Sales GDV_Total],

        'Version'[Version]=_ver,

        FILTER(

            ALL(Dim_Date),

            Dim_Date[Date] <= _MaxDate

        )

    )

2. 

Sales GDV_Cumulative_Last Mth =

VAR _maxDate = MAX('Fact_Direct Cost POC reallocate'[Construction period])

VAR _ver = SELECTEDVALUE('Version'[Version])

RETURN

    CALCULATE(

        [Sales GDV_Last Mth],

        'Version'[Version]=_ver,

        FILTER(

            ALLSELECTED(Dim_Date),

            Dim_Date[Date] <= _MaxDate

        )

    )

3. 

Test_GDV_CM-LM =

[Sales GDV_Cumulative]-[Sales GDV_Cumulative_Last Mth]

[Anonymous · Accepted solution]

Hi  @vic_cin ,

I created some data:

Measure follows the context of the "Total" row and is calculated in that context. Therefore, using a measure in a column of a table visualization may have unexpected values in the "Total" column.

You can use the following measure, using the IF()+ISINSCOPE() function, to determine if the specified level is the one, and if it is, then you can do the calculations according to your own needs, such as Average, Sum, etc. .

Here are the steps you can follow：

1. Create measure.

Measure =
var _last=
    SUMX(        FILTER(ALL('Table'),'Table'[Group1]=MAX('Table'[Group1])&&'Table'[Group2]=MAX('Table'[Group2])&&'Table'[Group3]=MAX('Table'[Group3])-1),[Sales GDV_Cumulative])
var _current=
    SUMX(
            FILTER('Table','Table'[Group3]=MAX('Table'[Group3])),[Sales GDV_Cumulative])
return
IF(
    ISINSCOPE('Table'[Group3])&&NOT(ISINSCOPE('Table'[Group4])),
   _current - _last
    ,[Test_GDV_CM-LM])

2. Result:

Best Regards,

Liu Yang

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