Forum Discussion

hasarinfareeth's avatar
hasarinfareeth
Frequent Visitor
1 year ago
Solved

Displaying weekly data for last 3 month in Matrix Table

Hi 

 

I need to display weekly data for last 3 months in Matrix table with the Month name marked above the week number. I have the dataset that can produce me the weekly data for each month using Time Inteligence through which I can show one month data in the table, however if I select another month, the data is data to be same week number to the existing month instead of displaying as a seperate row. 

Can anyone help me with this requirement, please?

 

Data for Dec'24:

 

Data for Nov'24:

 

Expected Result:

Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    Hi hasarinfareeth 

     

    Thanks for the reply from lbendlin and techies .

     

    hasarinfareeth , the following test is for your reference.

     

    Create a measure as follows

    Value = 
VAR _RANGEEND =
    CALCULATE ( MAX ( 'Date Table'[Date] ), ALL ( 'Date Table' ) )
VAR _RANGESTART =
    EOMONTH ( _RANGEEND, - 3 ) + 1
RETURN
    CALCULATE (
        COUNT ( 'Closed Tickets'[Ticket Reference] ),
        FILTER (
            'Closed Tickets',
            'Closed Tickets'[Closed On] >= _RANGESTART
                && 'Closed Tickets'[Closed On] <= _RANGEEND
        )
    )

     

    Click "Expand all down one level in the hierarchy".

     

    Output:

     

    If I update the data for February, the effect is as follows:

     

     

     

    Best Regards,
    Yulia Xu

     

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

6 Replies

  • lbendlin's avatar
    lbendlin
    Icon for Super User rankSuper User
    1 year ago

    weeks and months are incompatible.  Do you have a calendar table with your mapping?

     

    Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
    Do not include sensitive information. Do not include anything that is unrelated to the issue or question.
    Please show the expected outcome based on the sample data you provided.

    Need help uploading data? https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216
    Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

      • Anonymous's avatar
        Anonymous
        Not applicable
        1 year ago

        Hi hasarinfareeth 

         

        Thanks for the reply from lbendlin and techies .

         

        hasarinfareeth , the following test is for your reference.

         

        Create a measure as follows

        Value = 
VAR _RANGEEND =
    CALCULATE ( MAX ( 'Date Table'[Date] ), ALL ( 'Date Table' ) )
VAR _RANGESTART =
    EOMONTH ( _RANGEEND, - 3 ) + 1
RETURN
    CALCULATE (
        COUNT ( 'Closed Tickets'[Ticket Reference] ),
        FILTER (
            'Closed Tickets',
            'Closed Tickets'[Closed On] >= _RANGESTART
                && 'Closed Tickets'[Closed On] <= _RANGEEND
        )
    )

         

        Click "Expand all down one level in the hierarchy".

         

        Output:

         

        If I update the data for February, the effect is as follows:

         

         

         

        Best Regards,
        Yulia Xu

         

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

  • techies's avatar
    techies
    Icon for Super User rankSuper User
    1 year ago

    Hey there,

     

    To display weekly data for the last 3 months in a Matrix table with the month name above the week number, create a calculated column MonthWeek using this DAX:

    MonthWeek = FORMAT('Date'[Date], "MMM") & " - W" & (WEEKNUM('Date'[Date], 1) - WEEKNUM(STARTOFMONTH('Date'[Date]), 1) + 1)

    Then, create an IsLast3Months column to filter the data for the last 3 months:

    IsLast3Months = IF('Date'[Date] >= TODAY() - 90, "Last 3 Months", "Other")

    Add the MonthWeek column to the Rows in the Matrix, and filter using the IsLast3Months column by selecting "Last 3 Months" in the visual filter.

     

    Hope this helps!