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
Jkaelin
Resolver I
Resolver I

Can DAX calculate this? Calculate-Product-By Dates (Conceptual)

Good morning,

 

I have a data set, but prefer to keep it conceptual, then if possible, I will work on the actual dax formula.

 

I am attempting to calculate the product of a column by date, but calculated in this manner:  January's result * February's result * March's result = [Result I'm after].  

 

I am aware that I can use the calculate the product from Jan thru March, but my data set is stock returns, therefore, the returns have to be calculated by date & in order.  

 

Is this possible with dax?  Any tips or ideas??

 

Thank you & kindly,

James k.

 

 

1 ACCEPTED SOLUTION
v-huizhn-msft
Employee
Employee

Hi @Jkaelin,

I create the following sample table, the data is from 2013/1/1 to 5015/7/20. I think it is similar with your data set structure.

1.PNG

1. Create a calculate to get Month using the formula: 

Month = FORMAT(Stock[date],"MMM")
YEAR = YEAR(Stock[date])


2.PNG


2. Please create a measure based on the new table. Create a table visual to display the result. Please see the following screenshot, it shows January's result * February's result * March's result  in very year.

Result = CALCULATE(SUM(Stock[price]),FILTER(Stock,Stock[Month]="Jan"))*CALCULATE(SUM(Stock[price]),FILTER(Stock,Stock[Month]="Feb"))*CALCULATE(SUM(Stock[price]),FILTER(Stock,Stock[Month]="Mar"))


4.PNG

Don't hesitate to ask if you have any other issue.

Best Regards,
Angelia

View solution in original post

3 REPLIES 3
v-huizhn-msft
Employee
Employee

Hi @Jkaelin,

I create the following sample table, the data is from 2013/1/1 to 5015/7/20. I think it is similar with your data set structure.

1.PNG

1. Create a calculate to get Month using the formula: 

Month = FORMAT(Stock[date],"MMM")
YEAR = YEAR(Stock[date])


2.PNG


2. Please create a measure based on the new table. Create a table visual to display the result. Please see the following screenshot, it shows January's result * February's result * March's result  in very year.

Result = CALCULATE(SUM(Stock[price]),FILTER(Stock,Stock[Month]="Jan"))*CALCULATE(SUM(Stock[price]),FILTER(Stock,Stock[Month]="Feb"))*CALCULATE(SUM(Stock[price]),FILTER(Stock,Stock[Month]="Mar"))


4.PNG

Don't hesitate to ask if you have any other issue.

Best Regards,
Angelia

Thanks @v-huizhn-msft

 

This seems to work well.  I spent much time working on this yesterday.  Another solution for me was to create a "productx" function and use the date to summarize the results.  Both work great & thank you for looking into this for me!!

Greg_Deckler
Super User
Super User

You could create a SUMMARIZE table grouped by month and then measures off of that table to grab the numbers for Jan, Feb, March and multiply them together. Sample data would provide a more useful response.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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.