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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Sum of the amount of sales the previous 5 months

Hi;

 

I want to calculate the cumulative of sales quantity of the previous 5 months without counting the current also I want it to be dynamic depending on the current month.

 

Data example bellow

EXAMPLE DAX.JPG

 

1 ACCEPTED SOLUTION
Ashish_Mathur
Super User
Super User

Hi,

Drag this measure to a card visual

=CALCULATE([Cantidad],DATESBETWEEN(Calendar[Date],EDATE(EOMONTH(TODAY(),-1)+1,-5),EOMONTH(TODAY(),-1)+1))

Hope this helps.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

6 REPLIES 6
Ashish_Mathur
Super User
Super User

Hi,

Drag this measure to a card visual

=CALCULATE([Cantidad],DATESBETWEEN(Calendar[Date],EDATE(EOMONTH(TODAY(),-1)+1,-5),EOMONTH(TODAY(),-1)+1))

Hope this helps.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Anonymous
Not applicable

It Works, thank you.

You are welcome.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
MFelix
Super User
Super User

Hi @Anonymous ,

 

Try the following measure:

 

Sum Last 5 Months =
CALCULATE (
    SUM ( 'Table'[Quantity] );
    DATESINPERIOD ( 'Table'[Date]; MAX ( 'Table'[Date] ); -6; MONTH )
)
    - SUM ( 'Table'[Quantity] )

Replace the table name by the your data. If the date column is on a different table just change also that column to the correct one.

 

Regards,

MFelix


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Anonymous
Not applicable

It doesn't work because I have a data since 2016. Data Exp.JPG

 

The number of dates should no be an issue, since the calculations is made based on your date a going backward.

Did you tried the measure?

Can you share a mockup file?
Regards,
MFelix

Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.