DAX Running total measure - from start of financial year until end of current month

Question

Measure values keep going from the start of the financial year selected in page filter, to the end of the financial year selected in page filter. I need it to go from the start of the financial year selected in page filter, to the end of the current month. My measure is:

YTD Sales = 
    CALCULATE (
        sum(Revenue[Amount]), 
        DATESYTD(ForecastSalesDate[CalendarDate],"31/10")
    )

When I select financial year 2020 in page filter, I want it to show from 01/11/2019 upto 30/04/2020 (today is 16/04/2020), but it keeps going to 31/10/2020 (the financial year end):

What I am trying to get is:

The data model is:

Can anyone advise further?

Cheers for all help

Why not just add a 'Relative Date' filter at page/visual level? — Rory
Not sure why that would change anything? I already have a date filter at Page level, and its for financial year. Relative dates are for calendar years — Naz
You can add a relative date filter to exclude any data not in the past n calendar months, which would prevent your visual from displaying dates beyond 30/04 until May begins. — Rory
As an aside, that data model looks quite difficult to interpret. I'd suggest dragging your tables around so that they flow in the direction of one to many, or vice versa. — Rory
You could store the target months as dates like (01, month, year) and link them to the date column in your date dim, retaining the ability to summarise at month level. That said, I wouldn't bother at this point. As for your original issue, if the relative filter doesn't work, you could add a condition to your measure to return BLANK() when the date context is later than TODAY() — Rory

Naz Naz · Accepted Answer · 2020-04-17T12:28:44

Solution: Changed Target months to dates in format 01/mm/yyyy. Removed second Dates dim table. Added a new column to the remaining Dates dim table - MONTH(Calendardate - TODAY()). Then added this column into page filter, to be greater than or equal to 0

DAX Running total measure - from start of financial year until end of current month

1 Answers