Forum Discussion
Syndicate_Admin
3 years agoAdministrator
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: ...
- 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
3 years agoCommunity Champion
Please share sample data or a link to a sample PBIX file