Calculate Average Each January to Today in a CARD

[Anonymous]

Hi, i need to calculate an average from January every day to date selected, but, the calculate is in a Card is not in a table.  I have year and monthyear filter.

i was trying with CALCULATE function and datesbetween, but the stardate for me is "startofyear(date)", and when i select a monthyear in my filter, the value change for each month value. 

i.e: I want to calculate, total sales from january 2019 to July 2019 and divide this for month quantity or month max number(7 for july) in order to have a average, always since first month of year.

i.e: I want to calculate, total sales from january 2018 to march 2018 (selected month in the filter) and divide this for motnh quantity or month max number (3 for march) in order to have an average, always since first month of the year.

Thanks

[v-piga-msft]

Hi @Anonymous ,

For your requirement, you could refer to this measure below.

averag =
VAR start_date =
    STARTOFYEAR ( 'Sheet1'[Date] )
VAR selecteddate =
    MAX ( Sheet1[Date] )
VAR interval =
    DATEDIFF ( start_date, selecteddate, MONTH )
RETURN
    DIVIDE (
        CALCULATE (
            SUM ( 'Sheet1'[Revenue] ),
            FILTER (
                'Sheet1',
                'Sheet1'[Date] >= start_date
                    && 'Sheet1'[Date] <= selecteddate
            )
        ),
        interval
    )

Here is my test output.

You also could refer to my attachment.

Best Regards,

Cherry

[Anonymous]

hi, and thanks for the answers, @v-piga-msft @Cmcmahan  i was trying with the answer, but i have an error, the measure is not summarizing from start of year to max date (selected date), insted is summarizing just the selected month and calculating onlye one month average. 

For example: I have 12 months, the total of sales is 930, but, until March is 244 an his average must be 81,3. However the dax expression measure gave me 27,3. That is 244/3. (also in the interval, i have to add +1).

I think the problem is in calculate function, wich is not sumamarizing in the rank of dates, correctly. The function is sumarizing month per month, in consecuence the division is the sales of one month / quantity of months (month selected). 

Also i can see that the max(Date) give me back the last date in the calendar created, for my case with the complete calendar is 31/12/2019

I dont know how to solve.

Below an example of my data. 

Can you please, help me ? 

Thanks.  

My date format is DD/MM/YYYY

Year	Sales	Period	Date
2018	79	201801	01/01/2018
2018	83	201802	01/02/2018
2018	82	201803	01/03/2018
2018	79	201804	01/04/2018
2018	75	201805	01/05/2018
2018	78	201806	01/06/2018
2018	79	201807	01/07/2018
2018	90	201808	01/08/2018
2018	92	201809	01/09/2018
2018	80	201810	01/10/2018
2018	34	201811	01/11/2018
2018	79	201812	01/12/2018

[Cmcmahan]

Yeah, the previous measure seemed to be doing a lot of extra work. I hadn't actually looked at it until now.

I would simplify and use some of the built in DAX capability with TOTALYTD that evaluates an expression for all dates up to the max date currently filtered.  

AverageYTD = TOTALYTD(AVERAGEX(VALUES(SalesData[Period]), SUM(SalesData[Sales])), SalesData[Date])

The reason I'm using AVERAGEX instead of AVERAGE is because you need to group sales by the month which appears to be quickly indicated by the period in your data structure. 

[Anonymous]

Hi thanks for the answer @Cmcmahan , iam not tried yet, but you think with this dax can calculate the average summarizing since the first month of the year to the selected month. ?

Thanks again. 

[Cmcmahan]

I just double checked, and my previous measure returns the sum of each month's average.  You want the average of each month's average, so I've updated the measure to divide by the number of months being checked.

AverageYTD = TOTALYTD(DIVIDE(AVERAGEX(VALUES(SalesData[Period]), SUM(SalesData[Sales])),DISTINCTCOUNT(SalesData[Period])), SalesData[Date])

This may return odd results if you have an entire period with absolutely no entries, but that's usually a very unlikely case.

[Anonymous · Accepted solution]

hi @Cmcmahan  I have to say, thank u, I could find the solution with part of your dax. First i had to change my calendar table for one Autogenerated in power bi in order to have correct dates formats, then i use the next dax expression 

Average_to_month = DIVIDE(TOTALYTD(AVERAGEX(VALUES(Table[sales]), SUM(Table[sales])), Table[Date]), SELECTEDVALUE(Calendar[Number_mes]))

with this i could complete my requirement. 

[Anonymous]

hi, thanks again for the answer. @Cmcmahan i was trying and still not working, when i select 1 month, this calculate the average of one month. 

Example: Jan2018 = 80 Feb 2018 = 82 Mar 2018 = 70 the total is 232 and the avg is 77,33. When i select  in the filter feb 2018, the measure show me 82. I need the way to calculate sum total sales until the selected month, in feb case must be 162/2 = 81 and this is not happening. If u can please help me. 

THANKS A LOT, i am learning too much about ur answers.

[Cmcmahan]

That's weird.  Mine works just fine with the data you provided.  Check out my .pbix.  It has the behavior you're describing.

See if you can figure out what you're doing differently. I'd be interested in learning.

[Anonymous]

hi @Cmcmahan  the issue for me with your dax is: when u change the filter from slicer to a dropdown or list with the column period, the totalytd dosnt sum in date's rank. 

Example:

[Anonymous]

Hi @v-piga-msft  thanks for the answer, I have not tried it yet. I wanna know, if i change the slicer to a dropdown filter and, I Select 1 month, its will be take in power bi as Max(date) and the interval will be generated correctly. Is that correct ?

Thanks again!! 

[Cmcmahan]

Yes, interval and MAX(Date) will always be correct for the current slicer/filter selection.