Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply
NumeritasMartin
Frequent Visitor

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 value to be referenced is the one represented in the latest 'as at period'.

 

In the below table I only want to bring back the two values highlighted in green.

 

Date Filter.png

 

1 ACCEPTED SOLUTION
NumeritasMartin
Frequent Visitor

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.

View solution in original post

2 REPLIES 2
NumeritasMartin
Frequent Visitor

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
Continued Contributor
Continued Contributor

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] )
                    )
            )
        )
)

 

 

moizsherwani_0-1632240352841.png

 

Regards,

 

Moiz

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

 

 

Thanks,

Moiz
Was I able to answer your question? Mark my post as a solution to help others. Kudos if you liked the solution.

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.