Displaying weekly data for last 3 month in Matrix Table

[hasarinfareeth]

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:

[techies]

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!

[lbendlin]

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-...
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

[hasarinfareeth]

Thanks for your response @lbendlin. 

I have uploaded the sample pbix file in the below path having the fact table and the date table. 

https://drive.google.com/drive/folders/1z-uJniODHp0XlHZzSjfVtAhE0GxV9brX

the expected result is as below 

Thanks

Fareeth

[v-xuxinyi-msft · Accepted solution]

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.

[hasarinfareeth]

Thanks a lot @v-xuxinyi-msft . This has worked for me but the sorting is not as I expected. I alredy have a sorting based on the total number of tickets per team which is required. With this sorting the month is jumbled and is not as expected. Is there anything I am missing on the sorting? 

[v-xuxinyi-msft]

Hi @hasarinfareeth 

Could you please show a screenshot of the problem you are having? I sorted through the option shown and everything seems to be working fine. Is this what you wanted?

Best Regards,
Yulia Xu