Dividing the total of this month with total of same month last year

[Anonymous]

Hi all.,

I want to create a measure in which i can calculate the total of one month with total of same month in last year. like this i want to calculate for all 2019 months with 2018 months. 

[v-juanli-msft · Accepted solution]

Hi @Anonymous 

If i understand you correctly, you could create a calendar table

calendar = CALENDARAUTO()

Create a relationship between the two tables

Create measures in your data table

this year = SUM('Table'[value])

last year = CALCULATE([this year],SAMEPERIODLASTYEAR('calendar'[Date]))

divide = IF([last year]<>BLANK(),([this year]-[last year])/[this year])

Best Regards
Maggie

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

[v-juanli-msft · Accepted solution]

Hi @Anonymous 

If i understand you correctly, you could create a calendar table

calendar = CALENDARAUTO()

Create a relationship between the two tables

Create measures in your data table

this year = SUM('Table'[value])

last year = CALCULATE([this year],SAMEPERIODLASTYEAR('calendar'[Date]))

divide = IF([last year]<>BLANK(),([this year]-[last year])/[this year])

Best Regards
Maggie

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

[Anonymous]

Hi, 

Actually i want to calculate the sum of all months sale in 2018 and then i want to divide those particular sums with the sales of 2019. according to the point which you mentioned helped me in getting the idea how to do it but i am still not getting the right figures my example is like this maybe it can help you understand the problem better:

 CATEGORY(NEW AND OLD)

SALES 2018 (NEW+OLD)              SALES(ONLY NEW)2019              RATIO

150                                                130                                             130/150(86.66)

180                                                120                                             120/180(66.66)

i am here just trying to calculate the ratio.

Thanks for the help.

[v-juanli-msft · Accepted solution]

Hi @Anonymous 

Create a calendar table, then create relationship between it and your table

calendar = ADDCOLUMNS(CALENDARAUTO(),"year",YEAR([Date]),"month",MONTH([Date]))

Create measures 

2018 = CALCULATE(SUM('Table'[value]),FILTER(ALL('calendar'),'calendar'[year]=2018))

2019= CALCULATE(SUM('Table'[value]),FILTER(ALL('calendar'),'calendar'[year]=2019))

ration = [2019]/[2018]

If 2018 is the total sales from previous year till 2018 (eg, 2011~2018), change the "=" to "<="

2018 = CALCULATE(SUM('Table'[value]),FILTER(ALL('calendar'),'calendar'[year]<=2018))

Best Regards
Maggie

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