Forum Discussion

Chandrashekar's avatar
Chandrashekar
Icon for Resolver III rankResolver III
2 years ago
Solved

Average based on NMonth vs Month

Hello Team,   I need help with dax to get Average however I got it for count(Dax).   I need dax for average for below average_Lmonth = Last Nmonth -1(Jan-24, Feb-24, Mar-24, Apr-24). average = ...
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi Chandrashekar 

     

    Thanks for the reply from BeaBF . The following is my testing for your reference.

     

    I added some data in the table.

     

     

     

    Measure = 
VAR _Last1month =
    CALCULATE (
        COUNT ( 'Table'[Company] ),
        FILTER (
            'Table',
            [Month_Year] >= MIN ( slicer[Month_Year] )
                && [Month_Year]
                    <= DATE ( YEAR ( MAX ( slicer[Month_Year] ) ), ( MONTH ( MAX ( slicer[Month_Year] ) ) - 1 ), DAY ( MAX ( slicer[Month_Year] ) ) )
        )
    )
VAR _lastmonth =
    CALCULATE (
        COUNT ( 'Table'[Company] ),
        FILTER (
            'Table',
            [Month_Year] >= MIN ( slicer[Month_Year] )
                && [Month_Year] <= MAX ( slicer[Month_Year] )
        )
    )
RETURN
    DIVIDE ( _Last1month, _lastmonth )

     

     

     

    Output:

     

    Due to Security Policy, I am unable to open your link. If there is still a problem, please let me know the logic for calculating your desired result.

     

    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.

Chandrashekar's avatar
Chandrashekar
Icon for Resolver III rankResolver III
2 years ago

Hello,

 

Depends on selection from slicer. it may be old data(May be 2018 to till today. 

Final Intention is to check how client is doing.

Regards,

Chandrashekar B

 

Regards,

Chandrashekar B  

BeaBF's avatar
BeaBF
Icon for Super User rankSuper User
2 years ago

Chandrashekar 

 

Average_Selected_Range =
VAR SelectedMonthss = VALUES('Table'[Month_Year])
RETURN
CALCULATE(
    AVERAGE('Table'[SI NO]),
    FILTER(
        'Table',
        'Table'[Month_Year] IN SelectedMonthss
    )
)
 
Average_Previous_Range =
VAR SelectedMonths = VALUES('Table'[Month_Year])
VAR MaxMonth = MAX('Table'[Month_Year])
VAR PreviousMonths = EXCEPT(SelectedMonths, {MaxMonth})
RETURN
CALCULATE(
    AVERAGE('Table'[SI NO]),
    FILTER(
        'Table',
        'Table'[Month_Year] IN PreviousMonths
    )
)
  • Chandrashekar's avatar
    Chandrashekar
    Icon for Resolver III rankResolver III
    2 years ago

    Hello,

     

    Can we take average based on company instead of sl no.

     

    Regards,

    Chandrashekar B

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi Chandrashekar 

     

    Thanks for the reply from BeaBF . The following is my testing for your reference.

     

    I added some data in the table.

     

     

     

    Measure = 
VAR _Last1month =
    CALCULATE (
        COUNT ( 'Table'[Company] ),
        FILTER (
            'Table',
            [Month_Year] >= MIN ( slicer[Month_Year] )
                && [Month_Year]
                    <= DATE ( YEAR ( MAX ( slicer[Month_Year] ) ), ( MONTH ( MAX ( slicer[Month_Year] ) ) - 1 ), DAY ( MAX ( slicer[Month_Year] ) ) )
        )
    )
VAR _lastmonth =
    CALCULATE (
        COUNT ( 'Table'[Company] ),
        FILTER (
            'Table',
            [Month_Year] >= MIN ( slicer[Month_Year] )
                && [Month_Year] <= MAX ( slicer[Month_Year] )
        )
    )
RETURN
    DIVIDE ( _Last1month, _lastmonth )

     

     

     

    Output:

     

    Due to Security Policy, I am unable to open your link. If there is still a problem, please let me know the logic for calculating your desired result.

     

    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.

  • Chandrashekar's avatar
    Chandrashekar
    Icon for Resolver III rankResolver III
    2 years ago

    Hello,

     

    Thank you and will check.

     

    Regards,

    Chandrashekar B