Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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..
Solved! Go to 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
Hi,
Share some data to work with and show the expected result.
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
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
@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
tried, does not work..
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
User | Count |
---|---|
93 | |
83 | |
77 | |
75 | |
66 |
User | Count |
---|---|
115 | |
105 | |
93 | |
65 | |
60 |