Last Thirteen Month End Date calculation.

[Anonymous]

Hello All, 

Trying to do the calculation of sales for last thirteen month end dates. It should be only the month end date. I am using matrix visual where my month end date should get changed based on the selection of date in the slicer. So if choose today's date then it should automatically show only last 13 month end dates along with its sales figures. It's little bit tricky but I am trying a lot to find the appropriate solution.. One this is sure where I need use one more date dimension table but could not figure out.. Any help is like dream come true for me..

[Jihwan_Kim]

Hi, @Anonymous 

I tried to create a sample pbix file based on what I got from the explanation.

Please check the below picture and the sample pbix file's link down below.

Only Month End Sales by Axis =

VAR selectedaxis =

MAX ( 'Axis'[Index] )

VAR selecteddatemonthend =

EOMONTH ( MAX ( 'Calendar'[Date] ), - selectedaxis )

VAR lastsalesdateofeachmonth =

CALCULATE (

MAX ( Sales[Date] ),

FILTER ( ALL ( Sales ), Sales[Date] <= selecteddatemonthend )

)

RETURN

CALCULATE (

SUM ( Sales[Sales] ),

FILTER ( ALL ( Sales ), Sales[Date] = lastsalesdateofeachmonth )

)

Sales by Axis Total Fix =

SUMX( VALUES('Axis'[Month]), [Only Month End Sales by Axis])

https://www.dropbox.com/s/z92hnoaqvp4bh6m/sidd.pbix?dl=0 

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Linkedin: linkedin.com/in/jihwankim1975/

Twitter: twitter.com/Jihwan_JHKIM

[Fowmy]

@Anonymous 

Please refer to this video from sqlbi.com, you may need to adjust the model and calculation to suits your requirement.

https://www.youtube.com/watch?v=d8Rm7dwM6gc

[Anonymous]

Remember it's only last thirteen month end date dates calculation.. No cumulative calculation of the entire month only month end date.