Forum Discussion
Show data from previous weeks
- 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
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?
The week starts on Monday and ends on Friday.
- PaulDBrown3 years agoCommunity Champion
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
- Syndicate_Admin3 years agoAdministrator
It has been very helpful, thank you very much!
- PaulDBrown3 years agoCommunity Champion
Happy to have helped! If the suggestion has solved your request, please mark the relevant post as the solution to close the thread and help others looking for similar solutions