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
eliasayy
Impactful Individual
Impactful Individual

use if to find difference between columns from specific date

Hello, i want to find varriance = sum( tableA[cost]) - sum(tableB[cost]) .

i have data from before 2022, i want to use the varriance to calculate from 2022-01-28 until now while anything before just give me a 0 . so im assuming i use the if synatx?

any help?

3 REPLIES 3
v-rongtiep-msft
Community Support
Community Support

Hi @eliasayy ,

Do both table A and table B have a date column? If so, please have a try.

Create a measure.

measure=var _a=calculate(sum(tableA[cost]),filter(all(tableA),tableA[date]>=date(2022,1,28))
var _b=calculate(sum(tableB[cost]),filter(all(tableB),tableB[date]>=date(2022,1,28))
return
_a-_b

If I have misunderstood your meaning, pelase provide more details with your desired output and sample data without privacy information.

 

Best Regards

Community Support Team _ Polly

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Nathaniel_C
Super User
Super User

Hi @eliasayy ,

Try this 

Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
Nathaniel

 

 

Variance =
var _date = tableA [date]
var _TAsum = MAX( tableA[cost]) 
var _TBsum = MAX(tableB[cost])

Return If( _date > 1/27/2022,_TAsum-_TBsum, 0)

//assumes dates match between TableA and TableB drop this on your table visual, calcs row by row

 

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




it didnt work for me 

Helpful resources

Announcements
Fabcon_Europe_Social_Bogo

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.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.