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, espero que alguien podría por favor ayudarme con este informe im trabajando en. Necesito obtener la suma de cada semana durante las últimas 4 semanas. Todavía estaba funcionando bien el año pasado y yo estaba usando estas fórmulas, pero desde el comienzo de este año no es:
Los totales en este momento se supone que son WK1- 1195 , WK2 - 26, WK3 - 2203 y WK4 - 2386
Tengo una tabla de calendario
Estoy usando la consulta directa
¡Gracias!
Solved! Go to Solution.
@atjt217 ,
Según mi entendimiento, la fórmula debería ser así,
prevWEEK4 =
VAR __weekNum = WEEKNUM(TODAY(), 1)
VAR __year = YEAR(TODAY())
VAR __maxWeekNum =
MAXX(
FILTER( ALL(DateDimension), [Year] = __year - 1),
[Week_Number]
)
RETURN
SWITCH(
__weekNum = 1, CALCULATE( [Billed_total], FILTER( DateDimension, [Year] = __year - 1 && [Week_Number] = __maxWeekNum -3 ) ),
__weekNum = 2, CALCULATE( [Billed_total], FILTER( DateDimension, [Year] = __year - 1 && [Week_Number] = __maxWeekNum -2 ) ),
__weekNum = 3, CALCULATE( [Billed_total], FILTER( DateDimension, [Year] = __year - 1 && [Week_Number] = __maxWeekNum -1 ) ),
__weekNum = 4, CALCULATE( [Billed_total], FILTER( DateDimension, [Year] = __year - 1 && [Week_Number] = __maxWeekNum ) ),
CALCULATE( [Billed_total], FILTER( DateDimension, [Week_Number] = __weekNum-4 ) )
)
prevWEEK3 =
VAR __weekNum = WEEKNUM(TODAY(), 1)
VAR __year = YEAR(TODAY())
VAR __maxWeekNum =
MAXX(
FILTER( ALL(DateDimension), [Year] = __year - 1),
[Week_Number]
)
RETURN
SWITCH(
__weekNum = 1, CALCULATE( [Billed_total], FILTER( DateDimension, [Year] = __year - 1 && [Week_Number] = __maxWeekNum -2 ) ),
__weekNum = 2, CALCULATE( [Billed_total], FILTER( DateDimension, [Year] = __year - 1 && [Week_Number] = __maxWeekNum -1 ) ),
__weekNum = 3, CALCULATE( [Billed_total], FILTER( DateDimension, [Year] = __year - 1 && [Week_Number] = __maxWeekNum ) ),
CALCULATE( [Billed_total], FILTER( DateDimension, [Week_Number] = __weekNum -3 ) )
)
prevWEEK2 =
VAR __weekNum = WEEKNUM(TODAY(), 1)
VAR __year = YEAR(TODAY())
VAR __maxWeekNum =
MAXX(
FILTER( ALL(DateDimension), [Year] = __year - 1),
[Week_Number]
)
RETURN
SWITCH(
__weekNum = 1, CALCULATE( [Billed_total], FILTER( DateDimension, [Year] = __year - 1 && [Week_Number] = __maxWeekNum -1 ) ),
__weekNum = 2, CALCULATE( [Billed_total], FILTER( DateDimension, [Year] = __year - 1 && [Week_Number] = __maxWeekNum ) ),
CALCULATE( [Billed_total], FILTER( DateDimension, [Week_Number] = __weekNum -2 ) )
)
prevWEEK1 =
VAR __weekNum = WEEKNUM(TODAY(), 1)
VAR __year = YEAR(TODAY())
VAR __maxWeekNum =
MAXX(
FILTER( ALL(DateDimension), [Year] = __year - 1),
[Week_Number]
)
RETURN
SWITCH(
__weekNum = 1, CALCULATE( [Billed_total], FILTER( DateDimension, [Year] = __year - 1 && [Week_Number] = __maxWeekNum) ),
CALCULATE( [Billed_total], FILTER( DateDimension, [Week_Number] = __weekNum -1 ) )
)
Saludos
Lionel Chen
Si este post ayuda,entonces considere Aceptarlo como la solución para ayudar a los otros miembros a encontrarlo más rápidamente.
@atjt217 ,
Según mi entendimiento, la fórmula debería ser así,
prevWEEK4 =
VAR __weekNum = WEEKNUM(TODAY(), 1)
VAR __year = YEAR(TODAY())
VAR __maxWeekNum =
MAXX(
FILTER( ALL(DateDimension), [Year] = __year - 1),
[Week_Number]
)
RETURN
SWITCH(
__weekNum = 1, CALCULATE( [Billed_total], FILTER( DateDimension, [Year] = __year - 1 && [Week_Number] = __maxWeekNum -3 ) ),
__weekNum = 2, CALCULATE( [Billed_total], FILTER( DateDimension, [Year] = __year - 1 && [Week_Number] = __maxWeekNum -2 ) ),
__weekNum = 3, CALCULATE( [Billed_total], FILTER( DateDimension, [Year] = __year - 1 && [Week_Number] = __maxWeekNum -1 ) ),
__weekNum = 4, CALCULATE( [Billed_total], FILTER( DateDimension, [Year] = __year - 1 && [Week_Number] = __maxWeekNum ) ),
CALCULATE( [Billed_total], FILTER( DateDimension, [Week_Number] = __weekNum-4 ) )
)
prevWEEK3 =
VAR __weekNum = WEEKNUM(TODAY(), 1)
VAR __year = YEAR(TODAY())
VAR __maxWeekNum =
MAXX(
FILTER( ALL(DateDimension), [Year] = __year - 1),
[Week_Number]
)
RETURN
SWITCH(
__weekNum = 1, CALCULATE( [Billed_total], FILTER( DateDimension, [Year] = __year - 1 && [Week_Number] = __maxWeekNum -2 ) ),
__weekNum = 2, CALCULATE( [Billed_total], FILTER( DateDimension, [Year] = __year - 1 && [Week_Number] = __maxWeekNum -1 ) ),
__weekNum = 3, CALCULATE( [Billed_total], FILTER( DateDimension, [Year] = __year - 1 && [Week_Number] = __maxWeekNum ) ),
CALCULATE( [Billed_total], FILTER( DateDimension, [Week_Number] = __weekNum -3 ) )
)
prevWEEK2 =
VAR __weekNum = WEEKNUM(TODAY(), 1)
VAR __year = YEAR(TODAY())
VAR __maxWeekNum =
MAXX(
FILTER( ALL(DateDimension), [Year] = __year - 1),
[Week_Number]
)
RETURN
SWITCH(
__weekNum = 1, CALCULATE( [Billed_total], FILTER( DateDimension, [Year] = __year - 1 && [Week_Number] = __maxWeekNum -1 ) ),
__weekNum = 2, CALCULATE( [Billed_total], FILTER( DateDimension, [Year] = __year - 1 && [Week_Number] = __maxWeekNum ) ),
CALCULATE( [Billed_total], FILTER( DateDimension, [Week_Number] = __weekNum -2 ) )
)
prevWEEK1 =
VAR __weekNum = WEEKNUM(TODAY(), 1)
VAR __year = YEAR(TODAY())
VAR __maxWeekNum =
MAXX(
FILTER( ALL(DateDimension), [Year] = __year - 1),
[Week_Number]
)
RETURN
SWITCH(
__weekNum = 1, CALCULATE( [Billed_total], FILTER( DateDimension, [Year] = __year - 1 && [Week_Number] = __maxWeekNum) ),
CALCULATE( [Billed_total], FILTER( DateDimension, [Week_Number] = __weekNum -1 ) )
)
Saludos
Lionel Chen
Si este post ayuda,entonces considere Aceptarlo como la solución para ayudar a los otros miembros a encontrarlo más rápidamente.
Funciona. ¡Muchas gracias por su ayuda!
No @atjt217 ,
Por favor, intente esta fórmula de nuevo.
prevWEEK1 =
VAR __weekNum = WEEKNUM(TODAY(), 1)
VAR __year = YEAR(TODAY())
VAR __maxWeekNum =
MAXX(
FILTER( ALL(DateDimension), [Year] = __year - 1),
[Week_Number]
)
RETURN
IF(
__weekNum = 1,
CALCULATE(
[Billed_total],
FILTER(
DateDimension,
[Year] = __year - 1 && [Week_Number] = __maxWeekNum
)
),
CALCULATE(
[Billed_total],
FILTER(
DateDimension,
[Week_Number]= __weekNum - 1
)
)
)
Saludos
Lionel Chen
Si este post ayuda,entonces considere Aceptarlo como la solución para ayudar a los otros miembros a encontrarlo más rápidamente.
Hola, Gracias por la respuesta. Estaba trabajando para las semanas 1-3, pero la semana 4 está regresando en blanco.
Para la semana 4 aquí es lo que utilicé:
No @atjt217 ,
Cuando WEEKNUM(TODAY(), 1) es menor o igual que 4, la semana anterior, dos semanas, tres semanas y cuatro semanas deben ser las últimas semanas del año anterior.
Como el número de semana actual es 1:
prevWEEK1 =
VAR __weekNum = WEEKNUM(TODAY(), 2)
VAR __year = YEAR(TODAY())
VAR __maxWeekNum =
MAXX(
FILTER( ALL(Sheet5), [Year] = __year - 1),
[WeekNum]
)
RETURN
IF(
__weekNum = 1,
CALCULATE(
SUM(Sheet5[Value]),
FILTER(
Sheet5,
[Year] = __year - 1 && [WeekNum] = __maxWeekNum
)
),
CALCULATE(
SUM(Sheet5[Value]),
FILTER(
Sheet5,
[WeekNum] = __weekNum - 1
)
)
)
Saludos
Lionel Chen
Si este post ayuda,entonces considere Aceptarlo como la solución para ayudar a los otros miembros a encontrarlo más rápidamente.
Hola Lionel,
Traté de usar la fórmula:
@atjt217, consulte mi blog, tenga una columna adicional sobre el rango de la semana en la tabla de fechas y úsactlas. Si es necesario; puede tener tabla de fechas en power bi incluso en modo de consulta directa
Vea si esto puede ayudar
Power BI — Semana a semana y WTD
https://medium.com/@amitchandak.1978/power-bi-wtd-questions-time-intelligence-4-5-98c30fab69d3
https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-La...
https://www.youtube.com/watch?v=pnAesWxYgJ8
Tabla de fechas en power bi en consulta directa https://www.youtube.com/watch?v=cQfJ0GmQ5os&list=PLPaNVDMhUXGbKatyDdOhGbTL3xW2Xy6pA&index=7
Probé la fórmula en su blog como una medida calculada:
Pero im obtener un error que indica que la sintaxis ASC es incorrecta.
¿Podría decirme si me pierdo algo?
Gracias por ayudar a amitchandak!
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 |
---|---|
2 | |
2 | |
2 | |
2 | |
1 |