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

Monthly growth

Hi all, 

 

I need to create  measures to then use in a growth formula, but im having issues.

 

Here's what I have so far:

 

Current Sales = CALCULATE(SUM('Table'[PC_NBR]), LASTDATE('Table'[Date]))
Previous Sales = CALCULATE(SUM('Table'[PC_NBR]),DATEADD(LASTDATE('Table'[Date]),-1,MONTH))
Growth% = ([Current Sales]/[Previous Sales])-1
 
I want to show the growth rate based on the lastest month, OCT21.
 
While this work for Product A because i do have sales for OCT21, it does not return the proper growth % for Product B and C.
 
For product B, those measures are returning a gowth % of 100% because it calculates the growth based on the two latest months for B, although those two months are not the true "latest months". I.e. it calculates growth based on Aug21 and Jul21, but i would like it to calculate growth based on the real two latest months (OCT21 and SEPT21). In that case, i should be 0% since product B has no data for those months.
 
Same thing for Product C, it currently gives me a growth% of 100% (from aug21 to sept21) because it thinks the latest month is SEPT21 since i dont have sales for OCT21 for this product, but in reality the growth rate is -100% because it went from 2 sales in SEPT21 to zero in OCT21.

luigi_0-1633708784493.png

 

Any idea?

Thanks!

1 ACCEPTED SOLUTION
AllisonKennedy
Super User
Super User

@Anonymous I just ran into this problem with Stock levels, so gave up on using LASTDATE.

 

See if this measure will help for your requirements:

 

Stock On Hand Dozen Qty =
VAR _Date =
CALCULATE(MAX(Sales[Record_Date])
, KEEPFILTERS(Dates)
, ALL()
)
VAR _Result =
SUMX(
FILTER(Sales
, Sales[Record_Date] = _Date
)
, Sales[PC_NBR]
)
RETURN _Result

Please @mention me in your reply if you want a response.

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

View solution in original post

4 REPLIES 4
parry2k
Super User
Super User

@Anonymous are you looking for this

 

parry2k_0-1633733439278.png

 

 

 



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

AllisonKennedy
Super User
Super User

@Anonymous I just ran into this problem with Stock levels, so gave up on using LASTDATE.

 

See if this measure will help for your requirements:

 

Stock On Hand Dozen Qty =
VAR _Date =
CALCULATE(MAX(Sales[Record_Date])
, KEEPFILTERS(Dates)
, ALL()
)
VAR _Result =
SUMX(
FILTER(Sales
, Sales[Record_Date] = _Date
)
, Sales[PC_NBR]
)
RETURN _Result

Please @mention me in your reply if you want a response.

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

Anonymous
Not applicable

Hi Allison, 

 

Thanks for your help, it worked!

 

Luigi

You're welcome @Anonymous , glad it worked!


Please @mention me in your reply if you want a response.

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

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.