Average - Year to Date

[Anonymous]

Dear,

I need a help in doing the YTD media, and for the current year (2019) I have value until February, for other months this is like zero because I do not have the values ​​yet.

As in the picture above I can not count on zeros, however, in a few months of last year for example I have zero that I need to count in YTD.

[TeigeGao]

Hi DouglasFranco,

Could you please share the sample data and expected result to us? Based on my understanding, you want to calculate the average YTD.

In this scenario, we can create a calendar table using the following query:

calendar = ADDCOLUMNS(CALENDARAUTO(),"year",YEAR([Date]),"month",MONTH([Date]),"month/year",FORMAT([Date],"mmm")&"/"&YEAR([Date]))

Then create relationship between calendar table and data table, then we can use the following query to calculate the average YTD:

ytd2018 = TOTALYTD(AVERAGE(Data2018[KPIS]),'calendar'[Date])
ytd2019 = TOTALYTD(AVERAGE(Data2019[KPIS]),'calendar'[Date])

The result will like below:

Best Regards,

Teige

[Anonymous · Accepted solution]

Hello Teige,

Thanks for the answer!

I'll try to build based on that calendar. But are we bringing in every month of 2019? Well, I need to get them in what you referred to as zero.

The year to date would be an average of the year, but in 2019 with the zeroes that have the average.

Best Regards, 

Douglas