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.
Hola
He escrito una larga medida que ha funcionado como se esperaba. A la medida es bastante larga y algo de var se usa en otras medidas también, decido sacar var weekstart_ y var weekend_ y convertirlos en medida [weekstart] y [fin de semana]. Cito estas dos medidas en una nueva medida. Sin embargo, la nueva medida [Total Hrs Planned Weekly2] no funciona aquí. No filtra datos con valores [weekstart] y [weekend]. Por favor, ayuda.
Solved! Go to Solution.
Allí, @Jeanxyz;
Puede modificar la medida de la siguiente manera:
weekstart =
VAR year_ = CALCULATE(MAX ( Dim_Date[Year] ),ALLSELECTED(Dim_Date))
VAR week_ = CALCULATE(MAX ( Dim_Date[WeekNumber] ),ALLSELECTED('Dim_Date'))
VAR weekstart_ =
IF (
CALCULATE(MIN (Dim_Date[DayOfWeekNumber]),
FILTER (
ALL ( Dim_Date ),
Dim_Date[Year] = year_
&& Dim_Date[WeekNumber] = week_)) > 1,
CALCULATE (
MIN ( Dim_Date[Date] ),
FILTER (
ALL ( Dim_Date ),
Dim_Date[Year] = year_ - 1
&& Dim_Date[WeekNumber] = 53 )),
CALCULATE (
MIN ( Dim_Date[Date] ),
FILTER (
ALL ( Dim_Date ),
Dim_Date[Year] = year_
&& Dim_Date[WeekNumber] = week_) ))
RETURN weekstart_
weekend =
VAR year_ =
CALCULATE(MAX ( Dim_Date[Year] ),ALLSELECTED(Dim_Date))
VAR week_ =
CALCULATE(MAX ( Dim_Date[WeekNumber] ),ALLSELECTED(Dim_Date))
VAR weekend_ =
CALCULATE( IF (
CALCULATE (
MAX ( Dim_Date[DayOfWeekNumber] ),
FILTER (
ALL ( Dim_Date ),
Dim_Date[Year] = year_
&& Dim_Date[WeekNumber] = week_)) < 7,
CALCULATE (
MAX ( Dim_Date[Date] ),
FILTER (
ALL ( Dim_Date ),
Dim_Date[Year] = year_ + 1
&& Dim_Date[WeekNumber] = 1 ) ),
CALCULATE(MAX ( Dim_Date[Date]),
FILTER (
ALL ( Dim_Date ),
Dim_Date[Year] = year_
&& Dim_Date[WeekNumber] = week_) ) ))
RETURN weekend_
Total Hrs Planned Weekly =
VAR year_ =
SELECTEDVALUE ( Dim_Date[Year] )
VAR current_year_ =
YEAR ( TODAY () )
VAR max_week_ =
SWITCH (
TRUE (),
year_ < current_year_, MAX ( Dim_Date[WeekNumber] ),
MAXX (
FILTER ( ALL ( Dim_Date ), Dim_Date[Date] = TODAY () ),
Dim_Date[WeekNumber]
)
)
VAR total_records =
CALCULATE (
SUM ( Fact_Timesheets[Planned Working Time] ),
FILTER (
ALL ( Dim_Date ),
Dim_Date[Date] >= MAXX ( SUMMARIZE ( 'Dim_Date', "1", [weekstart] ), [1] )
&& Dim_Date[Date] <= MAXX ( SUMMARIZE ( 'Dim_Date', "1", [weekend] ), [1] )
&& Dim_Date[WeekNumber] < max_week_
),
Fact_Timesheets[Time Category ID] <> 12
)
RETURN
total_records
El resultado final se muestra a continuación:
Saludos
Apoyo a la comunidad Team_ Yalan Wu
Si esta publicación ayuda,entonces considere Aceptarla como la solución para ayudar a los otros miembros a encontrarla más rápidamente.
Allí, @Jeanxyz;
¿Está resuelto su problema? Si es así, ¿le importaría aceptar las respuestas útiles como soluciones? Entonces somos capaces de cerrar el hilo. Más personas que tienen el mismo requisito encontrarán la solución rápidamente y se beneficiarán aquí. Gracias.
Saludos
Apoyo a la comunidad Team_ Yalan Wu
Si esta publicación ayuda,entonces considere Aceptarla como la solución para ayudar a los otros miembros a encontrarla más rápidamente.
Allí, @Jeanxyz;
Puede modificar la medida de la siguiente manera:
weekstart =
VAR year_ = CALCULATE(MAX ( Dim_Date[Year] ),ALLSELECTED(Dim_Date))
VAR week_ = CALCULATE(MAX ( Dim_Date[WeekNumber] ),ALLSELECTED('Dim_Date'))
VAR weekstart_ =
IF (
CALCULATE(MIN (Dim_Date[DayOfWeekNumber]),
FILTER (
ALL ( Dim_Date ),
Dim_Date[Year] = year_
&& Dim_Date[WeekNumber] = week_)) > 1,
CALCULATE (
MIN ( Dim_Date[Date] ),
FILTER (
ALL ( Dim_Date ),
Dim_Date[Year] = year_ - 1
&& Dim_Date[WeekNumber] = 53 )),
CALCULATE (
MIN ( Dim_Date[Date] ),
FILTER (
ALL ( Dim_Date ),
Dim_Date[Year] = year_
&& Dim_Date[WeekNumber] = week_) ))
RETURN weekstart_
weekend =
VAR year_ =
CALCULATE(MAX ( Dim_Date[Year] ),ALLSELECTED(Dim_Date))
VAR week_ =
CALCULATE(MAX ( Dim_Date[WeekNumber] ),ALLSELECTED(Dim_Date))
VAR weekend_ =
CALCULATE( IF (
CALCULATE (
MAX ( Dim_Date[DayOfWeekNumber] ),
FILTER (
ALL ( Dim_Date ),
Dim_Date[Year] = year_
&& Dim_Date[WeekNumber] = week_)) < 7,
CALCULATE (
MAX ( Dim_Date[Date] ),
FILTER (
ALL ( Dim_Date ),
Dim_Date[Year] = year_ + 1
&& Dim_Date[WeekNumber] = 1 ) ),
CALCULATE(MAX ( Dim_Date[Date]),
FILTER (
ALL ( Dim_Date ),
Dim_Date[Year] = year_
&& Dim_Date[WeekNumber] = week_) ) ))
RETURN weekend_
Total Hrs Planned Weekly =
VAR year_ =
SELECTEDVALUE ( Dim_Date[Year] )
VAR current_year_ =
YEAR ( TODAY () )
VAR max_week_ =
SWITCH (
TRUE (),
year_ < current_year_, MAX ( Dim_Date[WeekNumber] ),
MAXX (
FILTER ( ALL ( Dim_Date ), Dim_Date[Date] = TODAY () ),
Dim_Date[WeekNumber]
)
)
VAR total_records =
CALCULATE (
SUM ( Fact_Timesheets[Planned Working Time] ),
FILTER (
ALL ( Dim_Date ),
Dim_Date[Date] >= MAXX ( SUMMARIZE ( 'Dim_Date', "1", [weekstart] ), [1] )
&& Dim_Date[Date] <= MAXX ( SUMMARIZE ( 'Dim_Date', "1", [weekend] ), [1] )
&& Dim_Date[WeekNumber] < max_week_
),
Fact_Timesheets[Time Category ID] <> 12
)
RETURN
total_records
El resultado final se muestra a continuación:
Saludos
Apoyo a la comunidad Team_ Yalan Wu
Si esta publicación ayuda,entonces considere Aceptarla como la solución para ayudar a los otros miembros a encontrarla más rápidamente.
Aquí hay una pantalla de impresión del resultado, la nueva medida ignora el filtro de semana y el ID de recurso (ID de empleado). Resume todas las horas del mes del año seleccionado.
cuando dices que no funciona, ¿no has presentado cuál es el problema?
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 |
---|---|
1 | |
1 | |
1 | |
1 | |
1 |