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 T...
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.

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!