Help with outphased row calculations based on dates

[Osganov]

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

[mahoneypat · Accepted solution]

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

[Osganov]

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

[Ashish_Mathur]

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. 

[Osganov]

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