YTD budget measure excluding the values of the future months

[Pim_V]

Dear all,

I would like to compare actual YTD figures with budget YTD figures.

How do i make sure that my YTD budget figure does not include the values of the budget from May until December 2017.

I would like to accomplish this without  adding a slicer or filter on the report page.

At this moment the visual compares the YTD actual value till april(because there are no future actuals) with the YTD budget value of whole 2017.

My current Budget YTD formula is:

Budget YTD Third Party Cashflow = CALCULATE(SUM('Cashflow Total'[Third party cash flow]); FILTER(ALL('Cashflow Total'[Year & Month]); 'Cashflow Total'[Year & Month] <= MAX('Cashflow Total'[Year & Month]))) 

The column 'Cashflow total'[Year & Month] contains full dates like dd-mm-yyyy.

I am looking forward to some help, thank you. 

[Anonymous · Accepted solution]

One way to solve this is to add "FutureDatesIndication" column in your Calendar Table.
For example: FutureDatesIndication = IF(CalendarTable[Date]>TODAY(),"Future","")
Then, add this FILTER to your CALCULATE:
FILTER(CalendarTable,CalendarTable[FutureDatesIndication]<>"Future")

[Pim_V]

Thanks for the reply.

I already figured it out myself. I used the same solution as you described.

I could not find the button to mark the post as solved before. But now i did.

[mattbrice]

Budget YTD Third Party Cashflow =
VAR LastActualDate =
    LASTNONBLANK ( 'Cashflow Total'[Year & Month]; [Actual Sales] )
RETURN
    CALCULATE (
        SUM ( 'Cashflow Total'[Third party cash flow] );
        FILTER (
            ALL ( 'Cashflow Total'[Year & Month] );
            'Cashflow Total'[Year & Month] <= LastActualDate
        )
    )

Where [Actual Sales] = SUM ( Table[Actual Sales Column] )

 or  a little bit simpler code:

Budget YTD Third Party Cashflow =
VAR LastActualDate =
    LASTNONBLANK ( 'Cashflow Total'[Year & Month]; [Actual Sales] )
RETURN
    TOTALYTD ( SUM ( 'Cashflow Total'[Third party cash flow] ); LastActualDate )