Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Syndicate_Admin
Administrator
Administrator

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:

Rosales22_0-1669439009417.png

Not all components have measurement record in all weeks, when trying to graph, I have something like this, for week 45 you only have a record in two components, and the others do not show values. What I am trying to do is that the components that do not have values in the week in which the filter is applied, show the values of the previous week, but if in the previous week they do not have values either, then look for one before and so on, until you find a week in which you have registration in that component.

Rosales22_2-1669439210740.png

I use two measurements, the first of which is to perform the calculation of CP, based on the deviation column:

Rosales22_4-1669439794288.png

The second measure is only to filter based on criteria, it is in this measure that it is used for the graph.

Rosales22_5-1669439907219.png

I hope I have been able to explain my problem correctly. Best regards.

1 ACCEPTED SOLUTION

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] )
    )

 

calendario.jpg

model.jpg

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

datos.jpg

Sample PBIX file attached





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






View solution in original post

9 REPLIES 9
PaulDBrown
Community Champion
Community Champion

Please share sample data or a link to a sample PBIX file





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






Hi, I've uploaded the Project to Drive.

The executable is located in the folder named "Project".

I hope you can help me, greetings.

Can you post the link to the file please?





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






This is the link to the file, which is inside the project folder:

https://drive.google.com/file/d/1-fFzjuizbDl4pHqIaTvATW142y0AvgNu/view?usp=share_link

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?





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






The week starts on Monday and ends on Friday.

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] )
    )

 

calendario.jpg

model.jpg

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

datos.jpg

Sample PBIX file attached





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






It has been very helpful, thank you very much!

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 





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.