Complex rolling 12 month calculation

[JimJim]

I have a problem that is a little too tricky for me, but probably not for you experts.

I have a measure in my report that calculates the rolling 12 month revenue, here is the measure:

Total Revenue USD RTM = 
var __lastVisibleDate = LASTDATE( 'Time'[Date] )
var __canMove12MBack =
	NOT ISBLANK( dateadd( __lastVisibleDate, -1, Year) )
var __result =
	CALCULATE(
	    SUM( GeneralLedger[USDRevenue] ),
	    DATESINPERIOD(
	    	'Time'[Date],
	    	__lastVisibleDate,
	    	-1,
	    	Year
	    )
	)	
return
if( __canMove12MBack, __result )

This will successfully calculate a 12 month period from today's date, but I need to to go back a little further than 1 year, to the beginning of the financial month.

To give you an example, if this calculation runs just now, it would calculate for all dates between 7 Jan 2021 and 7 Jan 2022, however for Jan 2021, the financial month started on 3 Jan 2021 so I would like it to calculate for dates 3 Jan 2021 to 7 Jan 2022.

I already have a date table with columns Date, FinancialMonth, FinancialYear, I just can't work it out

[v-yangliu-msft]

Hi  @JimJim ,

You can try to modify dax to the following form:

Total Revenue USD RTM =
VAR __lastVisibleDate =
    LASTDATE ( 'Time'[Date] )
VAR __canMove12MBack =
    NOT ISBLANK ( DATEADD ( __lastVisibleDate, -1, YEAR ) )
VAR __result =
    CALCULATE (
        SUM ( GeneralLedger[USDRevenue] ),
        FILTER (
            ALL ( GeneralLedger ),
            [Date] >= DATE ( 2021, 1, 3 )
                && [Date] <= _lastVisibleDate
        )
    )
RETURN
    IF ( __canMove12MBack, __result )

If it is not the result you want, can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Greg_Deckler]

@JimJim 

I would probably go with something like:

Total Revenue USD RTM = 
var __lastVisibleDate = MAX( 'Time'[Date] )
var __canMove12MBack =
	NOT ISBLANK( dateadd( __lastVisibleDate, -1, Year) )
var __back12Months = EOMONTH(__lastVisibleDate,-12)
var __firstDay = DATE(YEAR(__back12Months),MONTH(__back12Months),1)
var __result =
	CALCULATE(
	    SUM( GeneralLedger[USDRevenue] ),
	    FILTER(ALL('Time'),'Time'[Date] <= __lastVisibleDate && 'Time'[Date] >= __firstDay)
	)	
return
if( __canMove12MBack, __result )

You may find this helpful - https://community.powerbi.com/t5/Community-Blog/To-bleep-With-Time-Intelligence/ba-p/1260000

Also, 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...

[JimJim]

Hi @Greg_Deckler , thank you for taking the time to reply.

Am I correct in thinking that your calculation will always go back to the first day of the month? Which may not necessarily be the first financial date.

Edit. I also get an error on the DATEADD function, the parameter is not the correct type, it doesn't allow me to pass in the variable __lastVisibleDate

[Greg_Deckler]

@JimJim That is correct. I don't have enough information to know how you identify the first financial date of the month. I was just using your formula for that part. You could do it differently like just calculating the date like DATE(YEAR(__lastVisibleDate)-1,MONTH(__lastVisibleDate),DAY(__lastVisibleDate)) and then checking if it exists in your table.

I generally would avoid TI functions, especially if you are dealing with a financial calendar and not a standard Gregorian calendar.