cancel
Showing results 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

Helper II

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

Hi All,

I have 2 tables

1 table is a list of new products ex.
TBL_NewProduct

And have another TBL for the Invoice data

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

Thanks!

1 ACCEPTED SOLUTION
Super User

@mangchaaBI

``````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
7 REPLIES 7
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.

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
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
Helper II

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

Super User

@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
Helper II

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

Super User

@mangchaaBI

``````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
Helper II

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

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.