Forum Discussion

NumeritasMartin's avatar
NumeritasMartin
Frequent Visitor
4 years ago
Solved

Max / Last date on a filtered range

Hi All,   Hopefully an easy question.   I have a data set where the value of a period is stored every subsequent period, where the period value is the same but the 'as at period' is monthly. The ...
Need Help
Tips and Tricks
  • NumeritasMartin's avatar
    NumeritasMartin
    4 years ago

    I solved my issue.

    I created a calculated column which concatenated the elements which would impact my filter, then using that column as a variable I filtered my table using MAXX on the As At date. 

     

    So far the numbers reconcile.

moizsherwani's avatar
moizsherwani
Icon for Continued Contributor rankContinued Contributor
4 years ago

I am sure there is a more efficient way to to do this so I will let the experts improve on my answer (note this is a calculated table you need to create)

 

AsAtPeriod =
SUMMARIZE (
    'Table',
    'Table'[Data Item],
    "MAXDATE",
        CALCULATE (
            MAX ( 'Table'[As of Date] ),
            ALLEXCEPT ( 'Table', 'Table'[Data Item] )
        ),
    "VAL",
        CALCULATE (
            SUM ( 'Table'[Value] ),
            FILTER (
                'Table',
                'Table'[As of Date]
                    = CALCULATE (
                        MAX ( 'Table'[As of Date] ),
                        ALLEXCEPT ( 'Table', 'Table'[Data Item] )
                    )
            )
        )
)

 

 

 

Regards,

 

Moiz

If this post helps, please "Accept" it as Solution to help other members find it.