Dynamically giving a value to Last N months calculation

[Axiomite]

Hi there, 

What I am trying to achieve is to only show the values for the months smaller and equal to the selected month (slicer) but still but still only months in my financial year. The measure works if I manually add the number (in this case no. 6 below). 

But I would like write the measure to dynamically give the month number selected so I can be applied to the measure. Using the following measure I can calculate the number:

N months number =

VAR _start = STARTOFYEAR( dimDates[Date]) -1

VAR _end = ENDOFMONTH( dimDates[Date] )

RETURN

DATEDIFF( _start, _end, MONTH ) - 2

However, when adding it as a varialbe and using the variable for the N value in this case 6 it does not seem to work. The graph reverts to only the selected month.

Any suggestions? 
Kind regards,

Niel

[Anonymous]

Hi @Axiomite ,

Could you please share me with your pbix file after removing sensitive data?

Best Regards,
Eyelyn Qin

[Axiomite]

Hi @Anonymous ,

Let me know if you are able to access the pbix. 

Pbix File 

Many thanks for your reply.

Best Regards,

Niel

[Axiomite]

Hi @Anonymous ,

Let me know if you are able to access the pbix. 

Pbix File 

Many thanks for your reply.

Best Regards,

Niel

[Greg_Deckler]

@Axiomite Maybe:

N months number =
VAR _start = STARTOFYEAR( ALL(dimDates[Date])) -1
VAR _end = ENDOFMONTH( ALL(dimDates[Date]) )
RETURN
DATEDIFF( _start, _end, MONTH ) - 2

That said, I wouldn't recommend TI functions.

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