cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Syndicate_Admin
Administrator
Administrator

Segmentación de datos WTD

Hola

He creado la cortadora MTD / QTD e YTD en la misma rebanadora, pero quería tener la cortadora WTD ahora. Lo hice siguiendo el siguiente video:

https://www.youtube.com/watch?v=AdLDYohLeJc

El código que he utilizado para obtenerlo también está a continuación:

MTD/QTD/YTD =
VAR TodayDate = HOY()
VAR YearStart = CALCULATE(STARTOFYEAR(PuigWeeklySkuByStore_data2[Month Year]),YEAR(PuigWeeklySkuByStore_data2[Month Year])= YEAR(TodayDate)
VAR QuarterStart = CALCULATE(STARTOFQUARTER(PuigWeeklySkuByStore_data2[Month Year]),YEAR(PuigWeeklySkuByStore_data2[Month Year])=YEAR(TodayDate),QUARTER(PuigWeeklySkuByStore_data2[Month Year])= QUARTER( TodayDate))
VAR MonthStart = CALCULATE(STARTOFMONTH(PuigWeeklySkuByStore_data2[Month Year]),YEAR(PuigWeeklySkuByStore_data2[Month Year])= YEAR(TodayDate),MONTH(PuigWeeklySkuByStore_data2[Month Year])= MONTH( TodayDate))
Resultado VAR =
UNIÓN(
ADDCOLUMNS(
CALENDARIO(YearStart,TodayDate),
"Selección", "YTD"
),
ADDCOLUMNS(
CALENDARIO(QuarterStart,TodayDate),
"Selección","QTD"
),
ADDCOLUMNS(
CALENDARIO(MonthStart,TodayDate),
"Selección","MTD"
)
)
DEVOLUCIÓN
Resultado


Solo quiero agregar valor wtd en él. Sería muy útil para mí. Mi rebanadora se ve como a continuación:

jdfkj.PNG
Su ayuda sería muy útil para mí.
Gracias
1 ACCEPTED SOLUTION
Syndicate_Admin
Administrator
Administrator

Lea los comentarios de su enlace de video, tiene el código ... Utilicé lo mismo

var WeekStart = CALCULATE(TodayDate-WEEKDAY(TodayDate,2),YEAR(Data[Data])=YEAR(TodayDate))

Pruebe si esto funciona...

MTD/QTD/YTD = -- MTD/QTD/YTD/WTD

VAR TodayDate = TODAY()

VAR YearStart = CALCULATE(STARTOFYEAR(PuigWeeklySkuByStore_data2[Month Year]),YEAR(PuigWeeklySkuByStore_data2[Month Year]) = YEAR(TodayDate) )

VAR QuarterStart = CALCULATE(STARTOFQUARTER(PuigWeeklySkuByStore_data2[Month Year]),YEAR(PuigWeeklySkuByStore_data2[Month Year])=YEAR(TodayDate),QUARTER(PuigWeeklySkuByStore_data2[Month Year]) = QUARTER(TodayDate))

VAR MonthStart = CALCULATE(STARTOFMONTH(PuigWeeklySkuByStore_data2[Month Year]),YEAR(PuigWeeklySkuByStore_data2[Month Year]) = YEAR(TodayDate),MONTH(PuigWeeklySkuByStore_data2[Month Year]) = MONTH(TodayDate))

VAR WeekStart = CALCULATE(TodayDate-WEEKDAY(TodayDate,2),YEAR(PuigWeeklySkuByStore_data2[Month Year])=YEAR(TodayDate))


VAR Result =
     UNION(
          ADDCOLUMNS( CALENDAR(YearStart,TodayDate), "Selection", "YTD"),
          ADDCOLUMNS( CALENDAR(QuarterStart,TodayDate), "Selection","QTD"),
          ADDCOLUMNS( CALENDAR(MonthStart,TodayDate), "Selection","MTD"),
          ADDCOLUMNS( CALENDAR(WeekStart,TodayDate), "Selection","WTD")
     )

RETURN Result

Además, lea esta publicación de blog:

https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-La...

View solution in original post

4 REPLIES 4
Syndicate_Admin
Administrator
Administrator

Hay @Junaid11 ,

A lo que puedo ver en el código no tienes un valor de fecha en tu calendario, el único valor que tienes es el PuigWeeklySkuByStore_data2 [Mes Año] que supongo que es una sola fecha para cada mes, ¿es correcto?

Si no tienes la granularidad del día no puedes tener un valor WTD por lo que para ello necesitas añadirlo en el cálculo sería algo similar a esto:

MTD/QTD/YTD = 
VAR TodayDate =
    TODAY ()
VAR YearStart =
    CALCULATE (
        STARTOFYEAR ( 'calendar'[Date]),
        YEAR ( PuigWeeklySkuByStore_data2[Month Year] ) = YEAR ( TodayDate )
    )
VAR QuarterStart =
    CALCULATE (
        STARTOFQUARTER ( 'calendar'[Date] ),
        YEAR ( PuigWeeklySkuByStore_data2[Month Year] ) = YEAR ( TodayDate ),
        QUARTER ( PuigWeeklySkuByStore_data2[Month Year] ) = QUARTER ( TodayDate )
    )
VAR MonthStart =
    CALCULATE (
        STARTOFMONTH (  'calendar'[Date] ),
        YEAR ( PuigWeeklySkuByStore_data2[Month Year] ) = YEAR ( TodayDate ),
        MONTH ( PuigWeeklySkuByStore_data2[Month Year] ) = MONTH ( TodayDate )
    )
VAR WeekStart =
    CALCULATE (
        MIN('calendar'[Date])  - WEEKDAY(MIN('calendar'[Date]) , 2 ) + 1,
        YEAR ( PuigWeeklySkuByStore_data2[Month Year] ) = YEAR ( TodayDate ),
        MONTH ( PuigWeeklySkuByStore_data2[Month Year] ) = MONTH ( TodayDate )
    )


VAR Result =
    UNION (
        ADDCOLUMNS ( CALENDAR ( YearStart, TodayDate ), "Selection", "YTD" ),
        ADDCOLUMNS ( CALENDAR ( QuarterStart, TodayDate ), "Selection", "QTD" ),
        ADDCOLUMNS ( CALENDAR ( MonthStart, TodayDate ), "Selection", "MTD" ),
        ADDCOLUMNS ( CALENDAR ( WeekStart, TodayDate ), "Selection", "WTD" )
    )
RETURN
    Result
Syndicate_Admin
Administrator
Administrator

Lea los comentarios de su enlace de video, tiene el código ... Utilicé lo mismo

var WeekStart = CALCULATE(TodayDate-WEEKDAY(TodayDate,2),YEAR(Data[Data])=YEAR(TodayDate))

Pruebe si esto funciona...

MTD/QTD/YTD = -- MTD/QTD/YTD/WTD

VAR TodayDate = TODAY()

VAR YearStart = CALCULATE(STARTOFYEAR(PuigWeeklySkuByStore_data2[Month Year]),YEAR(PuigWeeklySkuByStore_data2[Month Year]) = YEAR(TodayDate) )

VAR QuarterStart = CALCULATE(STARTOFQUARTER(PuigWeeklySkuByStore_data2[Month Year]),YEAR(PuigWeeklySkuByStore_data2[Month Year])=YEAR(TodayDate),QUARTER(PuigWeeklySkuByStore_data2[Month Year]) = QUARTER(TodayDate))

VAR MonthStart = CALCULATE(STARTOFMONTH(PuigWeeklySkuByStore_data2[Month Year]),YEAR(PuigWeeklySkuByStore_data2[Month Year]) = YEAR(TodayDate),MONTH(PuigWeeklySkuByStore_data2[Month Year]) = MONTH(TodayDate))

VAR WeekStart = CALCULATE(TodayDate-WEEKDAY(TodayDate,2),YEAR(PuigWeeklySkuByStore_data2[Month Year])=YEAR(TodayDate))


VAR Result =
     UNION(
          ADDCOLUMNS( CALENDAR(YearStart,TodayDate), "Selection", "YTD"),
          ADDCOLUMNS( CALENDAR(QuarterStart,TodayDate), "Selection","QTD"),
          ADDCOLUMNS( CALENDAR(MonthStart,TodayDate), "Selection","MTD"),
          ADDCOLUMNS( CALENDAR(WeekStart,TodayDate), "Selection","WTD")
     )

RETURN Result

Además, lea esta publicación de blog:

https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-La...

Hola @sevenhills @MFelix ,

La solución está funcionando en la pestaña Datos, ya que puse el Dax que envió, muestra las fechas del DMT, pero lo puse en la tabla, también muestra otras fechas de semanas anteriores. He adjuntado fotos para que las entiendas. Debería mostrar las fechas de la semana actual, pero todavía muestra los datos de la semana anterior. Por favor, ayude a comprender qué está causando que haga esto.

sadeffeef.PNGsdffgg.PNG

Hay @Junaid11 ,

Pruebe el código siguiente:

MTD/QTD/YTD = 
VAR TodayDate =
    TODAY ()
VAR YearStart =
    CALCULATE (
        STARTOFYEAR ( PuigWeeklySkuByStore_data2[Month Year]),
        YEAR ( PuigWeeklySkuByStore_data2[Month Year] ) = YEAR ( TodayDate )
    )
VAR QuarterStart =
    CALCULATE (
        STARTOFQUARTER ( PuigWeeklySkuByStore_data2[Month Year] ),
        YEAR ( PuigWeeklySkuByStore_data2[Month Year] ) = YEAR ( TodayDate ),
        QUARTER ( PuigWeeklySkuByStore_data2[Month Year] ) = QUARTER ( TodayDate )
    )
VAR MonthStart =
    CALCULATE (
        STARTOFMONTH (  PuigWeeklySkuByStore_data2[Month Year] ),
        YEAR ( PuigWeeklySkuByStore_data2[Month Year] ) = YEAR ( TodayDate ),
        MONTH ( PuigWeeklySkuByStore_data2[Month Year] ) = MONTH ( TodayDate )
    )
VAR WeekStart =
    CALCULATE (
        Today()  - WEEKDAY(Today() , 2 ) + 1,
        YEAR ( PuigWeeklySkuByStore_data2[Month Year] ) = YEAR ( TodayDate ),
        MONTH ( PuigWeeklySkuByStore_data2[Month Year] ) = MONTH ( TodayDate )
    )


VAR Result =
    UNION (
        ADDCOLUMNS ( CALENDAR ( YearStart, TodayDate ), "Selection", "YTD" ),
        ADDCOLUMNS ( CALENDAR ( QuarterStart, TodayDate ), "Selection", "QTD" ),
        ADDCOLUMNS ( CALENDAR ( MonthStart, TodayDate ), "Selection", "MTD" ),
        ADDCOLUMNS ( CALENDAR ( WeekStart, TodayDate ), "Selection", "WTD" )
    )
RETURN
    Result

Helpful resources

Announcements
Microsoft Build 768x460.png

Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!

May UG Leader Call Carousel 768x460.png

What difference can a User Group make for you?

At the monthly call, connect with other leaders and find out how community makes your experience even better.

Power BI Release May 2022 768x460.png

Check it out!

Click here to read more about the May 2022 updates!

Top Kudoed Authors