Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Average - Year to Date

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 th...
Need Help
  • Anonymous's avatar
    Anonymous
    7 years ago

    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 

TeigeGao's avatar
TeigeGao
Icon for Solution Sage rankSolution Sage
7 years ago

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's avatar
Anonymous
Not applicable
7 years ago

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