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 p...
Need Help
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

    • fjcampos's avatar
      fjcampos
      Frequent Visitor
      6 years ago

      Try again please pbix