cancel
Showing results for
Did you mean:
Highlighted
Regular Visitor

## Help with outphased row calculations based on dates

Hi Community:

How can i create a measure that divides (or any calculation) de Units by the Forcast based on when Date 1 and Date 2 Match in a Table?

Thanks , i really appreciate your help and time spent on this inquiry.

Oscar

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User III

You can try using TREATAS for this one.

NewMeasure = var units = SUM(Table[Units])
var dates = VALUES(Table[Date 1])

var forecast = CALCULATE(SUM(Table[Forecast]), ALL(Table[Date 1), TREATAS(dates, Table[Date 2]))

return DIVIDE(units, forecast)

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

Proud to be a Super User!

4 REPLIES 4
Highlighted
Super User III

Hi,

Assuming columns A and B are one dataset and columns C and D are another, we should transform 4 columns into just 3 - Date, Units and Forecast.  This can happen by Merging the 2 datasets by the Date column in the Query Editor.

Let me know if this works for you.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Highlighted
Regular Visitor

Hi Ashish!

That is a good idea , unfortunatelly , i need Date 1 Column "clean" since i am using it for other calculations , by merging both date columns i can compromise the Data, any other thoughts?

I Really appreciate your time and help.

Thanks

Oscar

Highlighted
Super User III

You can try using TREATAS for this one.

NewMeasure = var units = SUM(Table[Units])
var dates = VALUES(Table[Date 1])

var forecast = CALCULATE(SUM(Table[Forecast]), ALL(Table[Date 1), TREATAS(dates, Table[Date 2]))

return DIVIDE(units, forecast)

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

Proud to be a Super User!

Highlighted
Regular Visitor

Hi Pat!

Thank you very much for your time and help , this works great! i really appreciate it .
Your solution reflects how an expert you are in Power BI congratulations!!
Thanks Again Pat and congratulations on being such a nice and supportive person and expert user.

Oscar

Announcements

#### Power Platform Community Conference

Check out the on demand sessions that are available now!

#### Microsoft Power Platform Communities

Check out the Winners!

#### Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors