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

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.