Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Measure Running Sum Up To A Point

Hi,

 

I have two tables:

1. List of development months (1 to 42)

2. List of transactions

 

There is a one to many relationship between tables 1 & 2.

 

I want to create a cumulative running total over development months like the example below.

However, I want the calulation to stop at certain points and preferably show null.

So for the 2017 column stop at month 30 / 2018 column stop at month 18 / 2019 column stop at month 6.

The red blocks I want to show NULL

 

The current measure is:

Gross Gross Premiums running total in DevelopmentMonth =
CALCULATE(
    SUM('Triangles_Merged'[Gross Gross Premiums]),
    FILTER(
        ALLSELECTED('Development Months'[DevelopmentMonth]),
        ISONORAFTER('Development Months'[DevelopmentMonth], MAX('Development Months'[DevelopmentMonth]), DESC)
 
    )
)
 

 

Any assistance will be great!

 

Thanks

 

Jason

Need Help
  • v-xuding-msft's avatar
    v-xuding-msft
    7 years ago

    Hi Anonymous ,

     

    I create a sample using the function of switch  you can have a try.

     

    Measure =
CALCULATE (
    SUM ( Triangles_Merged[Gross Gross Premiums] ),
    FILTER (
        ALLSELECTED ( Triangles_Merged ),    
                Triangles_Merged[DevelopmentMonth] <= MAX ( 'Development Months'[DevelopmentMonth] ) 
    ),VALUES(Triangles_Merged[year])
)
    Measure 2 =
SWITCH (
TRUE (),
MAX( Triangles_Merged[year] ) = 2017, CALCULATE (
'Development Months'[Measure],
FILTER ( 'Development Months', 'Development Months'[DevelopmentMonth] <= 30 )
),

MAX( Triangles_Merged[year] ) = 2018, CALCULATE (
'Development Months'[Measure],
FILTER ( 'Development Months', 'Development Months'[DevelopmentMonth] <= 18 )
),

MAX( Triangles_Merged[year] ) = 2019, CALCULATE (
'Development Months'[Measure],
FILTER ( 'Development Months', 'Development Months'[DevelopmentMonth] <= 6 )
),
BLANK ()
)

     

    Best Regards,

    Xue Ding

     

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

     

     

1 Reply

  • v-xuding-msft's avatar
    v-xuding-msft
    Community Support
    7 years ago

    Hi Anonymous ,

     

    I create a sample using the function of switch  you can have a try.

     

    Measure =
CALCULATE (
    SUM ( Triangles_Merged[Gross Gross Premiums] ),
    FILTER (
        ALLSELECTED ( Triangles_Merged ),    
                Triangles_Merged[DevelopmentMonth] <= MAX ( 'Development Months'[DevelopmentMonth] ) 
    ),VALUES(Triangles_Merged[year])
)
    Measure 2 =
SWITCH (
TRUE (),
MAX( Triangles_Merged[year] ) = 2017, CALCULATE (
'Development Months'[Measure],
FILTER ( 'Development Months', 'Development Months'[DevelopmentMonth] <= 30 )
),

MAX( Triangles_Merged[year] ) = 2018, CALCULATE (
'Development Months'[Measure],
FILTER ( 'Development Months', 'Development Months'[DevelopmentMonth] <= 18 )
),

MAX( Triangles_Merged[year] ) = 2019, CALCULATE (
'Development Months'[Measure],
FILTER ( 'Development Months', 'Development Months'[DevelopmentMonth] <= 6 )
),
BLANK ()
)

     

    Best Regards,

    Xue Ding

     

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