Forum Discussion

fjcampos's avatar
fjcampos
Frequent Visitor
6 years ago

Calculate the maximum values with a cut-off date

I am calculating a cumulative value at a cut-off date, but records that have already expired do not appear on subsequent cut-off dates. I would like to know how I can filter that data that ended in past months.
Below is an image of what I want to calculate

It's like a sum of the last record

Need Help

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi @fjcampos,

     

    Please check following steps as below and see if the result achieve your expectation:

    1. Create calculated table as silcer:

        Table 2 = DISTINCT('Table'[Date])

    2. Create measures:

        Measure =

         CALCULATE (

            MAX ( 'Table'[Date] ),

             FILTER (

                ALL ( 'Table' ),

                'Table'[Name] = MAX ( 'Table'[Name] )

                    && 'Table'[Date] <= SELECTEDVALUE ( 'Table 2'[Date] )

            )

        )

        Measure 2 =

        CALCULATE (

            SUM ( 'Table'[Sales] ),

            FILTER ( ALLSELECTED ( 'Table' ), 'Table'[Date] = [Measure] )

        )

    3. Result would be shown as below:

    BTW, Pbix as attached, hopefully works for you.

     

    Best Regards,

    Jay

     

    Community Support Team _ Jay Wang

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

    • fjcampos's avatar
      fjcampos
      Frequent Visitor
      6 years ago

      Thank you for your answer, but try to replicate your measure but it doesn't work.
      I attach an example file of how is my model Pbix

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        Hi fjcampos ,

         

        Couldn't achieve your Pbix, any sample data would be helpful.

         

        Thanks,

        Jay