Skip to main content
cancel
Showing results for
Search instead for
Did you mean:

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Anonymous
Not applicable

## Last Thirteen Month End Date calculation.

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

3 REPLIES 3
Super User

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])

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

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

Go to My LinkedIn Page

Super User

@Anonymous

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

Did I answer your question? Mark my post as a solution! and hit thumbs up
Anonymous
Not applicable

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

## Helpful resources

Announcements

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

#### Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

Top Solution Authors
Top Kudoed Authors