cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
muhammadumar Frequent Visitor
Frequent Visitor

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

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. 

 

2 ACCEPTED SOLUTIONS

Accepted Solutions
Community Support Team
Community Support Team

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

Hi @muhammadumar 

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])

Capture15.JPG

 

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.

View solution in original post

Community Support Team
Community Support Team

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

Hi @muhammadumar 

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]

Capture13.JPG

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.

View solution in original post

3 REPLIES 3
Community Support Team
Community Support Team

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

Hi @muhammadumar 

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])

Capture15.JPG

 

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.

View solution in original post

muhammadumar Frequent Visitor
Frequent Visitor

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

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.

Community Support Team
Community Support Team

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

Hi @muhammadumar 

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]

Capture13.JPG

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.

View solution in original post

Helpful resources

Announcements
Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Top Kudoed Authors
Users Online
Currently online: 53 members 1,177 guests
Please welcome our newest community members: