Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

dax query for month

hiii

i am looking for dax to display past 2 months and current month raw sla%, mitsla%,count of call closed based on my month slicer i have.

https://drive.google.com/file/d/1BmG-flA6TE0v_sbka6rRtY0Aa2h8EsVT/view?usp=sharing

 

Need Help
Show and Tell
  • Anonymous's avatar
    Anonymous
    5 years ago

    hi paul

    i have tried ur query but couldnt achieve the expected output

    can you check the expected output nd help me

     

    present outputexpected output

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Anonymous 

    With your current measure, the month name is the a text type column, you cannot use it to company with [month.no] that is a number. In fact you do not need a month name or month column, try just change it to company with today's month no, and use allselected() as the context. 

     

    R.SLA% 1 = 
CALCULATE (
    [RAW SLA%],
    FILTER (
        ALLSELECTED('Query1'),
        'Query1'[Call Closed].[MonthNo] <= MONTH(TODAY())
            && 'Query1'[Call Closed].[MonthNo] >= MONTH(TODAY()) - 2
            && 'Query1'[Call Closed].[Year] = YEAR ( TODAY() )
    )
)

    M.SLA% 1 = 
CALCULATE (
    [M.SLA%],
    FILTER (
        ALLSELECTED('Query1'),
        'Query1'[Call Closed].[MonthNo] <= MONTH(TODAY())
            && 'Query1'[Call Closed].[MonthNo] >= MONTH(TODAY()) - 2
            && 'Query1'[Call Closed].[Year] = YEAR ( TODAY () )
    )
)

     

    Paul Zheng _ Community Support Team
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      hi paul

      i have tried ur query but couldnt achieve the expected output

      can you check the expected output nd help me

       

      present outputexpected output

      • Anonymous's avatar
        Anonymous
        Not applicable
        5 years ago

        Anonymous 
        Sorry, I was misunderstood. I have looked into the pbix, I guess it is not possible to achieve your expect output.

         

        The problem you want to show 3 months data but only selected 1 month, you can use  dax measure filters the month range but only aggregates the 3 months data and displays the  result on the selected month. My suggestion is change the slicer to multi-selection, and hold on ctrl to select needed month name.

         

        However, I suggest you to create a month number column, and used the month number instead of month name in the report. 

         

        Paul Zheng _ Community Support Team
        If this post helps, please Accept it as the solution to help the other members find it more quickly.