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