cancel
Showing results for
Search instead 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!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.

@mahoneypa HoosierBI on YouTube

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!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.

@mahoneypa HoosierBI on YouTube

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
https://www.linkedin.com/in/excelenthusiasts/
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

## Helpful resources

Announcements

#### Happy New Year from Power BI

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

#### Check it Out!

Click here to read more about the December 2020 Updates!

#### Check it Out!

Click here to read the latest blog and learn more about contributing to the Power BI blog!

#### Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors