YTD to the last complete month


#1

Hi,

I have a requirement to report revenues YTD to the last completed month. So using today’s date of June 20, as an example, I need the YTD total for Jan to May, not including any revenue for June as the month hasn’t ended.

Any ideas how to create this?

Thanks!

Pete


#2

Easy way to solve this would be to have a date slicer, and just use total sales measure instead of Sales YTD. Would look something like this:

image


#3

Here’s a couple of ideas.

First there’s this one.

This may not solve the month cut off issue but it will certainly give you some ideas.

I think what you’ll ultimately want to do it create a calculated column which would enable you to place an additional filter on your results.

Something like this

Financial Record = 
VAR CurrentMonthYear = CALCULATE( SELECTEDVALUE( Dates[MonthnYear] ), Dates[Date] = TODAY() )

RETURN
IF( Dates[MonthnYear] < CurrentMonthYear, TRUE, FALSE )

This just gives you a way to create that additional filter that can be added to some logic in your formula.

See how you go with this idea.

Chrs


#4

Thanks Sam, that worked a treat. Just added a page filter of True for the new column and hey presto! The boss will be very happy! Nice one :smile:


#5

Nice one, chrs