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.

Reply
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
Jihwan_Kim
Super User
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.

 

Picture2.png

 

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


Fowmy
Super User
Super User

@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

 

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


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

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
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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

MayPowerBICarousel

Power BI Monthly Update - May 2024

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