Running total - from start of financial year until end of current month - but doesnt work

[ansa_naz]

It keeps 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 looking for is:

YearMonth2	YTD Sales
2019-11	656,105
2019-12	1,032,637
2020-01	1,732,107
2020-02	3,904,887
2020-03	4,973,156
2020-04	5,064,032

The data model is:

Can anyone advise further?

Cheers for all help

[ansa_naz · Accepted solution]

Got a solution - add a relative date filter to the page, to only show the last X months of data. Resolved

[v-frfei-msft]

Hi @ansa_naz ,

We can use a measure like that to work on it. For more details, please refer to this case.

https://community.powerbi.com/t5/Desktop/Filter-by-date-in-bar-chart/m-p/1026266#M483925

Year = 
VAR today =
    TODAY ()
VAR stofyear =
    DATE ( YEAR ( today ), 1, 1 )
VAR spre =
    DATE ( YEAR ( today ) - 1, 1, 1 )
VAR pre =
    EDATE ( today, -12 )
RETURN
    CALCULATE (
        SUM ( 'Table'[value] ),
        FILTER (
            'date',
            OR (
                'date'[Date] >= stofyear
                    && 'date'[Date] <= today,
                'date'[Date] >= spre
                    && 'date'[Date] <= pre
            )
        )
    )

[ansa_naz]

Hi @v-frfei-msft I have tried your solution as below:

YTD Sales Frank =
VAR today =
    TODAY ()
VAR stofyear =
    MIN ( ForecastSalesDate[CalendarDate] )
RETURN
    CALCULATE (
        SUM ( ANSAPBIProjectFRevenue[Amount] ),
        FILTER (
            ForecastSalesDate,
            ForecastSalesDate[CalendarDate] >= stofyear
                && ForecastSalesDate[CalendarDate] <= today
        )
    )

However this is not giving me a running total, it just gives me a total of sales per month, and then includes following months also as blank values:

What I am trying to get is:

YearMonth2	YTD Sales
2019-11	656,105
2019-12	1,032,637
2020-01	1,732,107
2020-02	3,904,887
2020-03	4,973,156
2020-04	5,064,032

Any further ideas would be very helpful please, many thanks

[jthomson]

Could you not use eomonth and today to pull the last date you're into as a variable, and then use that as part of an if statement, returning the existing measure if whatever field you're using is before that date and blank if it's after it?

[Greg_Deckler]

See if my Time Intelligence the Hard Way provides a different way of accomplishing what you are going for.

https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TIT...

[ansa_naz]

Hi @Greg_Deckler  I went through them, but I couldnt work out a way to use them, thank you for the reply