cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
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.

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.

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.

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.

Helpful resources

Announcements
Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Power BI Helps Homeless and Trouble Youth

Power BI Helps Homeless and Trouble Youth

We spoke with Power BI Super User, Greg Deckler, about his charity work

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 281 members 2,592 guests
Please welcome our newest community members: