Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
105 | |
79 | |
68 | |
63 |
User | Count |
---|---|
148 | |
107 | |
106 | |
82 | |
70 |