cancel
Showing results for
Did you mean:
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
Super User IV

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

Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

4 REPLIES 4
Super User IV

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

Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

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

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

Announcements

#### Happy New Year from Power BI

This is a must watch for a message from Power BI!