How to build YTD value based on the filtered month

[tuncay]

Hello community,

I try to build YTD based on the selected month.

For example:

February is selected → YTDvalue = (SUM(OEE) where MonthNo is 1 and 2)/2

see example:

OEE_ACT_YTD = 
var plant = SELECTEDVALUE('DPC Raw Data query for Power BI'[Org. Unit.Level 04])
var getmonth = SELECTEDVALUE('DPC Raw Data query for Power BI' [Calendar Year/Month.Calendar Year/Month Level 01].[MonthNo])
var z = CALCULATE(AVERAGE('DPC Raw Data query for Power BI'[OEE - Overall Equipment Effectiveness [%]]]), FILTER( ALL('DPC Raw Data query for Power BI'), 
[Version.Version Level 01.Key] = "103" &&
[Calendar Year/Month.Calendar Year/Month Level 01].[Jahr] = YEAR(TODAY()) &&
[Org. Unit.Level 04] = plant))
return z

But it always considers all months. When I select January it does not consider only Januar it considers all months and makes the average of all..

[Ashish_Mathur]

Hi,

Share some data to work with and show the expected result.

[Timuran]

Hello @tuncay ,

it looks like you miss to add getmonth in your filter.

Would be like this :

OEE_ACT_YTD = 
var plant = SELECTEDVALUE('DPC Raw Data query for Power BI'[Org. Unit.Level 04])
var getmonth = SELECTEDVALUE('DPC Raw Data query for Power BI' [Calendar Year/Month.Calendar Year/Month Level 01].[MonthNo])
var z = CALCULATE(AVERAGE('DPC Raw Data query for Power BI'[OEE - Overall Equipment Effectiveness [%]]]), FILTER( ALL('DPC Raw Data query for Power BI'), 
[Version.Version Level 01.Key] = "103" &&
[Calendar Year/Month.Calendar Year/Month Level 01].[Jahr] = YEAR(TODAY()) &&
[Org. Unit.Level 04] = plant && [Calendar Year/Month.Calendar Year/Month Level 01].[MonthNo] = getmonth))
return z

 Have a nice day

[tuncay]

but then it only considers the selected month, e. g. when February is selected it only Sums FEB data but it should sum JAN AND FEB

[Timuran]

@tuncay , juste replace = with <=

Like this :

OEE_ACT_YTD = 
var plant = SELECTEDVALUE('DPC Raw Data query for Power BI'[Org. Unit.Level 04])
var getmonth = SELECTEDVALUE('DPC Raw Data query for Power BI' [Calendar Year/Month.Calendar Year/Month Level 01].[MonthNo])
var z = CALCULATE(AVERAGE('DPC Raw Data query for Power BI'[OEE - Overall Equipment Effectiveness [%]]]), FILTER( ALL('DPC Raw Data query for Power BI'), 
[Version.Version Level 01.Key] = "103" &&
[Calendar Year/Month.Calendar Year/Month Level 01].[Jahr] = YEAR(TODAY()) &&
[Org. Unit.Level 04] = plant && [Calendar Year/Month.Calendar Year/Month Level 01].[MonthNo] = getmonth))
return z

Have a nice day

[tuncay]

tried, does not work..

[Timuran · Accepted solution]

OEE_ACT_YTD = 
var plant = SELECTEDVALUE('DPC Raw Data query for Power BI'[Org. Unit.Level 04])
var getmonth = SELECTEDVALUE('DPC Raw Data query for Power BI' [Calendar Year/Month.Calendar Year/Month Level 01].[MonthNo])
var z = CALCULATE(AVERAGE('DPC Raw Data query for Power BI'[OEE - Overall Equipment Effectiveness [%]]]), FILTER( ALL('DPC Raw Data query for Power BI'), 
[Version.Version Level 01.Key] = "103" &&
[Calendar Year/Month.Calendar Year/Month Level 01].[Jahr] = YEAR(TODAY()) &&
[Org. Unit.Level 04] = plant && [Calendar Year/Month.Calendar Year/Month Level 01].[MonthNo] <= getmonth))
return z

I forget to change it in the measure, now it would work