Stacked Column Chart Last Date issue

[Tad]

Hello,

I have the below stacked column charts. One is showing the totals by day and the other is showing the totals on the last day of the selected date range. The problem I have is the dark blue bar in the middle. It is a 1.2M amount at 35% probabiliy that was changed to 95% probability on the 21st. For some reason it is showing up in my measure that is also copied below. I'm assuming I need a summarize function as filtering is not acheiving what I need. I need the last date to be dynamic with the user's selection and I need it to work with the date hierarchy.

I've tried variations of both of the below measures:

Option 1 =

SUMX(

    FILTER (

        'Cross-Joined Date/Deals',

        VAR _V = Calculate ( MAX ( 'Cross-Joined Date/Deals'[Date]), ALLSELECTED('Cross-Joined Date/Deals'))

        RETURN

        'Cross-Joined Date/Deals'[Date] = _V

    ),

    'Cross-Joined Date/Deals'[Amount]

)

Option 2 =

CALCULATE(

    sum('Cross-Joined Date/Deals'[Amount]),

       FILTER( 'Cross-Joined Date/Deals', 'Cross-Joined Date/Deals'[Date] = LASTDATE('Cross-Joined Date/Deals'[Date]))

)

[Tad · Accepted solution]

Just in case anyone stumbles onto this and has a similar issue I fixed it by removing day from the hierarchy and just using IF(ISFILTERED(table[date].[Month]/.[Quarter]/.[Year] statements with ENDOF_____ MONTH/QUARTER/YEAR.