Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
1 year ago
Solved

Show 26 weeks in matrix

i have a fact table which has completed date and i have date table with start of the week as monday.
i want to show measure value for next 26 weeks based on month from slicer selection. Currently its showing all months. if i select march, it should start value from march only

 

Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    Thanks for the reply from vanessafvg , please allow me to provide another insight:

    Hi, Anonymous 
    Thanks for reaching out to the Microsoft fabric community forum.

    Regarding the issue you raised, my solution is as follows:

    Firstly, you need to create a calculated table to serve as a new slicer. Please ensure that this table does not have any relationships with other tables:

    DateTable = 
ADDCOLUMNS(
    CALENDAR(DATE(2020, 1, 1), DATE(2025, 12, 31)), 
    "Year", YEAR([Date]),
    "Month", FORMAT([Date], "MMMM"),
    "Month Number", MONTH([Date]),
    "Weekday", FORMAT([Date], "dddd"),
    "Weekday Number", WEEKDAY([Date], 2)
)

    2.Secondly, use the following measure as a filter:

    MEASURE =
VAR selectedmonth =
    CALCULATE ( MIN ( 'DateTable'[Date] ), ALLSELECTED ( 'DateTable' ) )
VAR cd =
    MAX ( 'Table'[Date] )
VAR f =
    IF (
        YEAR ( cd ) = YEAR ( selectedmonth )
            && MONTH ( cd ) >= MONTH ( selectedmonth )
            && DATEDIFF ( cd, selectedmonth, WEEK ) <= 26,
        1,
        0
    )
RETURN
    f

     

     

    3.Here's my final result, which I hope meets your requirements.

    You may need to note that the matrix has a column display limit of 100 columns. For further details, please refer to:

     Solved: Missing Columns in Matrix - Microsoft Fabric Community
     

    Please find the attached pbix relevant to the case.

     

    Best Regards,

    Leroy Lu

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

3 Replies

  • vanessafvg's avatar
    vanessafvg
    Icon for Community Champion rankCommunity Champion
    1 year ago

    Have you linked your date dim and fact table? ie your completed date to date in the date table?

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      1 year ago

      Thanks for the reply from vanessafvg , please allow me to provide another insight:

      Hi, Anonymous 
      Thanks for reaching out to the Microsoft fabric community forum.

      Regarding the issue you raised, my solution is as follows:

      Firstly, you need to create a calculated table to serve as a new slicer. Please ensure that this table does not have any relationships with other tables:

      DateTable = 
ADDCOLUMNS(
    CALENDAR(DATE(2020, 1, 1), DATE(2025, 12, 31)), 
    "Year", YEAR([Date]),
    "Month", FORMAT([Date], "MMMM"),
    "Month Number", MONTH([Date]),
    "Weekday", FORMAT([Date], "dddd"),
    "Weekday Number", WEEKDAY([Date], 2)
)

      2.Secondly, use the following measure as a filter:

      MEASURE =
VAR selectedmonth =
    CALCULATE ( MIN ( 'DateTable'[Date] ), ALLSELECTED ( 'DateTable' ) )
VAR cd =
    MAX ( 'Table'[Date] )
VAR f =
    IF (
        YEAR ( cd ) = YEAR ( selectedmonth )
            && MONTH ( cd ) >= MONTH ( selectedmonth )
            && DATEDIFF ( cd, selectedmonth, WEEK ) <= 26,
        1,
        0
    )
RETURN
    f

       

       

      3.Here's my final result, which I hope meets your requirements.

      You may need to note that the matrix has a column display limit of 100 columns. For further details, please refer to:

       Solved: Missing Columns in Matrix - Microsoft Fabric Community
       

      Please find the attached pbix relevant to the case.

       

      Best Regards,

      Leroy Lu

      If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.