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

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

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
November Carousel

Fabric Community Update - November 2024

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

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

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