cancel
Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Reply
kevderbeste
Helper III
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

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. 

View solution in original post

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

Capture.PNG

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.

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

YearSalesPeriodDate 
20187920180101/01/2018
20188320180201/02/2018
20188220180301/03/2018
20187920180401/04/2018
20187520180501/05/2018
20187820180601/06/2018
20187920180701/07/2018
20189020180801/08/2018
20189220180901/09/2018
20188020181001/10/2018
20183420181101/11/2018
20187920181201/12/2018

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. 

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. 

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.

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. 

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.

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.

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: powerbi.JPG

 

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

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

Helpful resources

Announcements
PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Community News

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors