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.

lbendlin's avatar
lbendlin
Super 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.

    • hasarinfareeth's avatar
      hasarinfareeth
      Frequent Visitor
      1 year ago

      Thanks a lot Anonymous . 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? 

      • Anonymous's avatar
        Anonymous
        Not applicable
        1 year ago

        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