Forum Discussion

Syndicate_Admin's avatar
Syndicate_Admin
Administrator
3 years ago
Solved

Show data from previous weeks

Hello, I hope you can help me with the following. I have a table of weekly measurements on car components classified as PDD, PDI, PTD, PTI, TT and TD. The structure of the table is as follows: ...
  • PaulDBrown's avatar
    PaulDBrown
    3 years ago

    Ok, I suggest you create a Date Table (Calendario) and set up the model joining the Date field in the Calendario Table to the date fields in your dim tables as follows:

    New table with:

     

    Calendario =
VAR _Ddate =
    CALCULATETABLE (
        DISTINCT ( Dim[Fecha] ),
        FILTER ( Dim, NOT ISBLANK ( Dim[Fecha] ) )
    )
VAR _DPercDate =
    CALCULATETABLE (
        DISTINCT ( 'Dim Perc'[Date] ),
        FILTER ( 'Dim Perc', NOT ISBLANK ( 'Dim Perc'[Date] ) )
    )
VAR _List =
    DISTINCT ( UNION ( _Ddate, _DPercDate ) )
VAR _MinDate =
    MINX ( _List, [Fecha] )
VAR _MaxDate =
    MAXX ( _List, [Fecha] )
RETURN
    ADDCOLUMNS (
        CALENDAR ( _MinDate, _MaxDate ),
        "MesNume", MONTH ( [Date] ),
        "Mes", FORMAT ( [Date], "MMM", "ES" ),
        "Semana", WEEKNUM ( [Date], 2 ),
        "YYYYWW",
            YEAR ( [Date] ) * 100
                + WEEKNUM ( [Date], 2 ),
        "Semana Año",
            "W" & WEEKNUM ( [Date], 2 ) & " "
                & YEAR ( [Date] ),
        "Año", YEAR ( [Date] )
    )

     

    Now you can use the fields from the Calendario table in visuals, measures, filters and slicers.
    Create a measure for each CP following this pattern:

     

    Último CP rojo =
VAR _MXW =
    CALCULATE (
        MAX ( Calendario[YYYYWW] ),
        FILTER (
            ALL ( Calendario ),
            Calendario[YYYYWW] <= MAX ( Calendario[YYYYWW] )
                && NOT ISBLANK ( [Cp Rojo] )
        )
    )
RETURN
    CALCULATE (
        [Cp Rojo],
        FILTER ( ALL ( Calendario ), Calendario[YYYYWW] = _MXW )
    )

     

    and you will get

    Sample PBIX file attached

PaulDBrown's avatar
PaulDBrown
Community Champion
3 years ago

Can you post the link to the file please?

  • PaulDBrown's avatar
    PaulDBrown
    Community Champion
    3 years ago

    Does the week start on Monday or Sunday?
    Also, in the image of the report page, where are you defining the week? or is it the depiction for the max week?

    • Syndicate_Admin's avatar
      Syndicate_Admin
      Administrator
      3 years ago

      The week starts on Monday and ends on Friday.

      • PaulDBrown's avatar
        PaulDBrown
        Community Champion
        3 years ago

        Ok, I suggest you create a Date Table (Calendario) and set up the model joining the Date field in the Calendario Table to the date fields in your dim tables as follows:

        New table with:

         

        Calendario =
VAR _Ddate =
    CALCULATETABLE (
        DISTINCT ( Dim[Fecha] ),
        FILTER ( Dim, NOT ISBLANK ( Dim[Fecha] ) )
    )
VAR _DPercDate =
    CALCULATETABLE (
        DISTINCT ( 'Dim Perc'[Date] ),
        FILTER ( 'Dim Perc', NOT ISBLANK ( 'Dim Perc'[Date] ) )
    )
VAR _List =
    DISTINCT ( UNION ( _Ddate, _DPercDate ) )
VAR _MinDate =
    MINX ( _List, [Fecha] )
VAR _MaxDate =
    MAXX ( _List, [Fecha] )
RETURN
    ADDCOLUMNS (
        CALENDAR ( _MinDate, _MaxDate ),
        "MesNume", MONTH ( [Date] ),
        "Mes", FORMAT ( [Date], "MMM", "ES" ),
        "Semana", WEEKNUM ( [Date], 2 ),
        "YYYYWW",
            YEAR ( [Date] ) * 100
                + WEEKNUM ( [Date], 2 ),
        "Semana Año",
            "W" & WEEKNUM ( [Date], 2 ) & " "
                & YEAR ( [Date] ),
        "Año", YEAR ( [Date] )
    )

         

        Now you can use the fields from the Calendario table in visuals, measures, filters and slicers.
        Create a measure for each CP following this pattern:

         

        Último CP rojo =
VAR _MXW =
    CALCULATE (
        MAX ( Calendario[YYYYWW] ),
        FILTER (
            ALL ( Calendario ),
            Calendario[YYYYWW] <= MAX ( Calendario[YYYYWW] )
                && NOT ISBLANK ( [Cp Rojo] )
        )
    )
RETURN
    CALCULATE (
        [Cp Rojo],
        FILTER ( ALL ( Calendario ), Calendario[YYYYWW] = _MXW )
    )

         

        and you will get

        Sample PBIX file attached