cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Helper III

## Calculate Average Each January to Today in a CARD

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

1 ACCEPTED SOLUTION
Helper III

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.
11 REPLIES 11
Resident Rockstar

Hi @kevderbeste ,

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

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

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
Resident Rockstar

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.

Helper III

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.

Resident Rockstar

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.

Helper III

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.
Helper III

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.

Resident Rockstar

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.

Helper III

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:

Helper III

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!!

Resident Rockstar

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

Announcements

#### Fabric certifications survey

Certification feedback opportunity for the community.

#### Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

#### Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors