cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Resolver I
Resolver I

Measure that should display the sum, in the period selected, of last time data available

Good morning,

 

Looking for a measure that can sum a value, in the period selected (also time series graph), of the last time data available.  The below image shows the yellow values I'm after.  I want it to dynamically always pull the latest available (but not data that would be beyond the date selected).  

 

Just as an FYI, I've tried several last date, max date, measures/formulas from this forum & SQLBI to no avail.

 

 

LastDate Power BI.PNG

 

Any tips?  Thoughts?  Help!?

 

Thank you & Kindly,

James

1 ACCEPTED SOLUTION
Community Support
Community Support

Hi @Jkaelin ,

I created a sample you can have a try.

  • create a calendar table and a column of month as a slicer.
Table 2 = CALENDARAUTO()
Month = MONTH('Table 2'[Date])

1.PNG

  • create a calculated column of month in original table and create a measure to implement it.
Month = MONTH('Table'[Date])

Measure = 
VAR a =
    SELECTEDVALUE ( 'Table 2'[Month] )
RETURN
    IF (
        ISFILTERED ( 'Table 2'[Month] ),
        CALCULATE (
            SUM ( 'Table'[Value] ),
            FILTER (
                'Table',
                'Table'[Month] = a
                    || 'Table'[Month]
                        = CALCULATE ( MAX ( 'Table'[Month] ), ALLEXCEPT ( 'Table', 'Table'[City] ) )
                        && CALCULATE ( MAX ( 'Table'[Month] ), ALLEXCEPT ( 'Table', 'Table'[City] ) ) < a
            )
        ),
        SUM ( 'Table'[Value] )
    )

2.PNG3.PNG

Best Regards,

Xue

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

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.

View solution in original post

1 REPLY 1
Community Support
Community Support

Hi @Jkaelin ,

I created a sample you can have a try.

  • create a calendar table and a column of month as a slicer.
Table 2 = CALENDARAUTO()
Month = MONTH('Table 2'[Date])

1.PNG

  • create a calculated column of month in original table and create a measure to implement it.
Month = MONTH('Table'[Date])

Measure = 
VAR a =
    SELECTEDVALUE ( 'Table 2'[Month] )
RETURN
    IF (
        ISFILTERED ( 'Table 2'[Month] ),
        CALCULATE (
            SUM ( 'Table'[Value] ),
            FILTER (
                'Table',
                'Table'[Month] = a
                    || 'Table'[Month]
                        = CALCULATE ( MAX ( 'Table'[Month] ), ALLEXCEPT ( 'Table', 'Table'[City] ) )
                        && CALCULATE ( MAX ( 'Table'[Month] ), ALLEXCEPT ( 'Table', 'Table'[City] ) ) < a
            )
        ),
        SUM ( 'Table'[Value] )
    )

2.PNG3.PNG

Best Regards,

Xue

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

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.

View solution in original post

Helpful resources

Announcements
secondImage

Happy New Year from Power BI

This is a must watch for a message from Power BI!

December Update

Check it Out!

Click here to read more about the December 2020 Updates!

Community Blog

Check it Out!

Click here to read the latest blog and learn more about contributing to the Power BI blog!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors