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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
mangchaaBI
Helper II
Helper II

Calculate a Column Where it Outputs the Sum of the Total Amount for each item WITH date filtter

Hi All,

 

Have another query that wish to get your inputs please

 

I have 2 tables 

 

1 table is a list of new products ex.
TBL_NewProduct

mangchaaBI_0-1701687961901.png

And have another TBL for the Invoice data

 

mangchaaBI_1-1701687979107.png

 

Here's the output that I would want to achieve as an additional column, so basically if the invoicedate is within the DateLaunched and MaxNewProductListed dates then it will be included in the sum of the output

mangchaaBI_2-1701688040789.png

 

Thanks!



1 ACCEPTED SOLUTION

@mangchaaBI 

Same as my orgianl reply

New Column = 

VAR __LaunchDate = TBL_NewProduct[DateLaunched]
	VAR __ListedDate = TBL_NewProduct[MaxNewProductListed]
	VAR __Product =  TBL_NewProduct[ItemName]
	RETURN
	CALCULATE(
		SUM(TBL[Total]),
		TBL[InvoiceDate] >= __LaunchDate,
		TBL[InvoiceDate] <= __ListedDate,
		TBL[ItemName] <= __Product	
	)
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

View solution in original post

7 REPLIES 7
v-shex-msft
Community Support
Community Support

Hi @mangchaaBI ,

Did the above suggestions help with your scenario? if that is the case, you can consider Kudo or Accept the helpful suggestions to help others who faced similar requirements.

If these also don't help, please share more detailed information and description to help us clarify your scenario to test.

How to Get Your Question Answered Quickly 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
Fowmy
Super User
Super User

@mangchaaBI 

Use this meaure:

	VAR __LaunchDate = TBL_NewProduct[DateLaunched]
	VAR __ListedDate = TBL_NewProduct[MaxNewProductListed]
	VAR __Product =  TBL_NewProduct[ItemName]
	RETURN
	CALCULATE(
		SUM(TBL[Total]),
		TBL[InvoiceDate] >= __LaunchDate,
		TBL[InvoiceDate] <= __ListedDate,
		TBL[ItemName] <= __Product	
	)
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

hi @Fowmy Thanks for taking the time to answer, just ran into a bump where I can't seem to add the variables into the equation, thanks

 

mangchaaBI_0-1701856855435.png

 

@mangchaaBI 

Sorry, Please add this code as a calcualted column to your TBL_NewProduct table.

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

hi @Fowmy sorry but can't find any code on your last reply, thank you

@mangchaaBI 

Same as my orgianl reply

New Column = 

VAR __LaunchDate = TBL_NewProduct[DateLaunched]
	VAR __ListedDate = TBL_NewProduct[MaxNewProductListed]
	VAR __Product =  TBL_NewProduct[ItemName]
	RETURN
	CALCULATE(
		SUM(TBL[Total]),
		TBL[InvoiceDate] >= __LaunchDate,
		TBL[InvoiceDate] <= __ListedDate,
		TBL[ItemName] <= __Product	
	)
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

Thank you I think this is what I need, problem is that I'm getting a low resource error on my pBI desktop app 😅 cant confirm if this is the fix for me, but I'll tag it as the solution for anyone who wants to try

Helpful resources

Announcements
Sept PBI Carousel

Power BI Monthly Update - September 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

Sept NL Carousel

Fabric Community Update - September 2024

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