DAX calculation for MAX, MIN and Average based on current month for previous Sales data.

[xcrmadmin]

Hello Experts 

iam looking for calaulation for MAX, MIN and Average of current month based on prevoioues sales of lats years and month. 

as you see i create examble of 3 cards and i need to get thoes values based on the calculation data return from previoues years and month. so suppose we are in February month so i need to show in thoes cards the MAX, MIN and Average based on 02-February of last years. what the DAX calaulation suggestion can be used for thoes cases.  

[Anonymous · Accepted solution]

Hi @xcrmadmin ,

Create 2 columns first.

_year = YEAR('Table'[date])

_month = MONTH('Table'[date])

Then create measures.

averagexvalue = var _monthtoday=MONTH(TODAY())
return
AVERAGEx(FILTER(ALL('Table'),'Table'[_month]=_monthtoday),[values_measure])

maxxvalue = var _monthtoday=MONTH(TODAY())
return
MaxX(FILTER(ALL('Table'),'Table'[_month]=_monthtoday),[values_measure])

minxvalue = var _monthtoday=MONTH(TODAY())
return
MINX(FILTER(ALL('Table'),'Table'[_month]=_monthtoday),[values_measure])

Best Regards
Community Support Team _ Polly

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi @xcrmadmin ,

Now I'm wondering what exactly is the outcome you want? The minimum for February this year? The minimum from last February? It says something different every time!

Can you provide some simple data and the output you want? It better be clear and unambiguous!

How to Get Your Question Answered Quickly 

Best Regards
Community Support Team _ Polly

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[xcrmadmin]

Brother The minimum from last February of last years

see in below image the feb of last years we need get the min one and this becouse we are now in feb, so based on the curent month we need to show the min, max and avg of the month of last years 2018 2019 2020 2021 2022  

[Anonymous · Accepted solution]

Hi @xcrmadmin ,

Create 2 columns first.

_year = YEAR('Table'[date])

_month = MONTH('Table'[date])

Then create measures.

averagexvalue = var _monthtoday=MONTH(TODAY())
return
AVERAGEx(FILTER(ALL('Table'),'Table'[_month]=_monthtoday),[values_measure])

maxxvalue = var _monthtoday=MONTH(TODAY())
return
MaxX(FILTER(ALL('Table'),'Table'[_month]=_monthtoday),[values_measure])

minxvalue = var _monthtoday=MONTH(TODAY())
return
MINX(FILTER(ALL('Table'),'Table'[_month]=_monthtoday),[values_measure])

Best Regards
Community Support Team _ Polly

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[xcrmadmin]

Brother i applied the samble you provide its same what iam have, but the issue now that its bring the min value of current year i dont need the current year going to this calculation, i need only compare the min values of previoues years even if the curen year is the minimum value. So your new dax 90% fix my issue but the result with me is not correct as its return the current year in the calclulation and the month not end yet. So i dont need to add or to compare the curent year in your Dax i need only compare the previoues years only. 

[Anonymous · Accepted solution]

Hi @xcrmadmin ,

Please refer to my pbix file directly.

Best Regards
Community Support Team _ Polly

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[xcrmadmin]

Thank you very much for your help this work for me. 

[Anonymous]

Hi @xcrmadmin ,

I have created a simple sample, please refer to my pbix file to see if it helps you.

Create  measures.

averagexvalue = var _month=MONTH(TODAY())
var _year=YEAR(TODAY())
return
AVERAGEX(FILTER(ALL('Table 1'),'Table 1'[month]=_month&&'Table 1'[year]=_year-1),('Table 1'[value]))

maxcalue = var _month=MONTH(TODAY())
var _year=YEAR(TODAY())
return
MAXX(FILTER(ALL('Table 1'),'Table 1'[month]=_month&&'Table 1'[year]=_year-1),('Table 1'[value]))

minvalue = var _month=MONTH(TODAY())
var _year=YEAR(TODAY())
return
MINX(FILTER(ALL('Table 1'),'Table 1'[month]=_month&&'Table 1'[year]=_year-1),('Table 1'[value]))

How to Get Your Question Answered Quickly 

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards
Community Support Team _ Polly

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[xcrmadmin]

Thank you so much for your response. Actually its look like your measure used per date (Day) for current month but in my examble iam looking per current month per year.  

see in my screen shot we are in Feb and i looking for min, max and AVG for feb for previoues years per current month.

😞

[FreemanZ]

hi @xcrmadmin 

could you paste some sample data, including at least date- and revenue-relevant columns?

[xcrmadmin]

Hello thank your for your response. 
i already send the sample in atthemnt 

[Anonymous]

Hi @xcrmadmin ,

Where is the sample? I am just calculating the value of the previous year for the current month.

Average value of last year:

averagexvalue = var _month=MONTH(TODAY())
var _year=YEAR(TODAY())
return
AVERAGEX(FILTER(ALL('Table 1'),'Table 1'[month]=_month&&'Table 1'[year]=_year-1),('Table 1'[value]))

 How to provide sample data in the Power BI Forum - Microsoft Power BI Community

How to Get Your Question Answered Quickly 

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards
Community Support Team _ Polly

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[xcrmadmin]

iam not able to download data as iam connecting to online dynamics. What iam looking for is get min, Max and avarage per year per each month not for last year  

[Anonymous]

Hi @xcrmadmin ,

Please have a try.

averagexvalue =
AVERAGEX (
    FILTER (
        ALL ( 'Table 1' ),
        'Table 1'[month] = SELECTEDVALUE ( 'Table 1'[month] )
            && 'Table 1'[year] = SELECTEDVALUE ( 'Table 1'[year] )
    ),
    ( 'Table 1'[value] )
)

maxxvalue =
MAXX (
    FILTER (
        ALL ( 'Table 1' ),
        'Table 1'[month] = SELECTEDVALUE ( 'Table 1'[month] )
            && 'Table 1'[year] = SELECTEDVALUE ( 'Table 1'[year] )
    ),
    ( 'Table 1'[value] )
)

MINXvalue =
MINXX (
    FILTER (
        ALL ( 'Table 1' ),
        'Table 1'[month] = SELECTEDVALUE ( 'Table 1'[month] )
            && 'Table 1'[year] = SELECTEDVALUE ( 'Table 1'[year] )
    ),
    ( 'Table 1'[value] )
)

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards
Community Support Team _ Polly

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[xcrmadmin]

Brother any help please as i said you almost done but i need per month your calculation return min value per row so i thing sum DAX should be added in your DAX 😞

[xcrmadmin]

Thank you brother we near to the solution, actually your DAX get the smallest value per row per each month for that for Feb we have sales order with 100$ in feb month, you almost near but we dont need per order we need to get the Min per month, so in this case we expect to see  63600 $ as small sum for feb month compared with other feb in previoues years.  what you provide is get the smalest order value per feb per old years and this nearest to what we need but we need it per month as bulk (total per month compared with this month with old years with same month )

[xcrmadmin]

This what i appled based on your solution and we get unexpected value 

MINX (
    FILTER (
        ALL ( DIM_WON_ANALYTICS ),
        DIM_WON_ANALYTICS[MON-TH] = value(SELECTEDVALUE ( 'Date'[MonthNum] ))
            && DIM_WON_ANALYTICS[YE-AR] = value(SELECTEDVALUE ( 'Date'[Year] ))
    ),
    ( DIM_WON_ANALYTICS[Actual Revenue] )   
)

[xcrmadmin]

Please Help 😞

[xcrmadmin]

here in my examble in feb month 63600 is the value per feb month compared for 2022 and old years