cancel
Showing results for
Did you mean:
Frequent Visitor

## Value difference by Date and categorial variable

Hi all

I want to calculate the difference between values, based on the date as well as another categorial variable.

All that should be displayed in a waterfall diagram (column Expected Result). I attached a screenshot with dummy data and the expectet outcome.

1 ACCEPTED SOLUTION
Community Support

Hi @angryvoegi ，

Based on your description, you can do some steps as follows.

1. Create a column to mark month.

`Month = MONTH('Diff'[Date])`

2.  Create a new measure.

`Test Value =var x1=MAXX(FILTER(ALL(Diff),[Month]=SELECTEDVALUE(Diff[Month])&&'Diff'[Cat]=SELECTEDVALUE(Diff[Cat])),'Diff'[Value])var x2=MAXX(FILTER(ALL(Diff),[Month]=SELECTEDVALUE(Diff[Month])-1&&'Diff'[Cat]=SELECTEDVALUE(Diff[Cat])),'Diff'[Value])returnx1-x2`

Result:

Hope that's what you were looking for.

Best Regards,

Yuna

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

5 REPLIES 5
Super User III

Hi,

Try this calculated column formula

=data[value]-lookupvalue(data[value],data[date],calculate(max(data[date]),filter(data,data[cat]=earlier(data[cat])&&data[date]<earlier(data[date]))),data[cat],data[cat])

Hope this helps.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Community Support

Hi @angryvoegi ，

Based on your description, you can do some steps as follows.

1. Create a column to mark month.

`Month = MONTH('Diff'[Date])`

2.  Create a new measure.

`Test Value =var x1=MAXX(FILTER(ALL(Diff),[Month]=SELECTEDVALUE(Diff[Month])&&'Diff'[Cat]=SELECTEDVALUE(Diff[Cat])),'Diff'[Value])var x2=MAXX(FILTER(ALL(Diff),[Month]=SELECTEDVALUE(Diff[Month])-1&&'Diff'[Cat]=SELECTEDVALUE(Diff[Cat])),'Diff'[Value])returnx1-x2`

Result:

Hope that's what you were looking for.

Best Regards,

Yuna

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

Frequent Visitor

Hi, thank you very much for your help!! It worked perfectly 🙂

Thanks again!

Super User IV

@angryvoegi , Try a new column like

new column =
var _max = maxx(filter(Table, [cat] =earlier([cat]) && [Date] <earlier([Date])),[Date])
return
[value] - maxx(filter(Table, [cat] =earlier([cat]) && [Date] =_max),[value])

Proud to be a Super User!

Frequent Visitor

Unfortunately I get an error ("syntax for return is incorrect").

Thanks again 🙂

Announcements

#### The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

#### Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.

Top Solution Authors
Top Kudoed Authors