cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Pim_V Frequent Visitor
Frequent Visitor

YTD budget measure excluding the values of the future months

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. 

1 ACCEPTED SOLUTION

Accepted Solutions
mshparber Established Member
Established Member

Re: YTD budget measure excluding the values of the future months

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")
3 REPLIES 3
mattbrice Senior Member
Senior Member

Re: YTD budget measure excluding the values of the future months

 

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 )
mshparber Established Member
Established Member

Re: YTD budget measure excluding the values of the future months

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 Frequent Visitor
Frequent Visitor

Re: YTD budget measure excluding the values of the future months

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.

Helpful resources

Announcements
Back to School Contest

Back to School Contest

Engage and empower students with Power BI!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Summit Australia 2019

Summit Australia 2019

Travel to Melbourne and network with thousands of peers!

Top Ideas
Users Online
Currently online: 261 members 2,961 guests
Please welcome our newest community members: