cancel
Showing results for
Did you mean:
Highlighted
Frequent Visitor

## Difference between previous mont and shop

Hi, i ask a question few days ago and the answer works me very well for some things, but i need to understand how to do this.

I have a pivot table like this.

 Shops Year Month Amount Shop1 2019 6 50 Shop1 2019 7 30 shop2 2019 6 40 Shop2 2019 7 20

And i want to make a Measure that make the difference between shop amount by months.

So measure will be like

Shop1 2019 7 20

Shop2 2019 7 20

And go on with multiple shops and differents years and months.

I search for youtube but only found with one shop, so the code works but not for me.

I understand this seems like asking to much, but i just trying my best to understand this. Sorry.

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User IV

@isaac152, Use time intelligence with the date. if you don't have it, create one as

Date: date([year],[month],1)

Example

``````MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))
last month Sales = CALCULATE(SUM(Sales[Sales Amount]),previousmonth('Date'[Date]))
last MTD (complete) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-1,MONTH))))
previous month value =  CALCULATE(sum('table'[total hours value]),previousmonth('Date'[Date]))

diff = [MTD Sales]-[last MTD Sales]
diff % = divide([MTD Sales]-[last MTD Sales],[last MTD Sales])``````

To get the best out of the weather intelligence feature. Make sure that you have a date calendar and that it has been marked as the date in the model view. Also, join her with the date column of her made/s. See:
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

See if my Time Intelligence webinar can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-Y...

Proud to be a Super User!

3 REPLIES 3
Highlighted
Super User II

Is the pivot table your rawdata or it's the visual table in powerbi? What's your rawdata looks like?

Proud to be a Super User!

Highlighted
Super User IV

@isaac152, Use time intelligence with the date. if you don't have it, create one as

Date: date([year],[month],1)

Example

``````MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))
last month Sales = CALCULATE(SUM(Sales[Sales Amount]),previousmonth('Date'[Date]))
last MTD (complete) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-1,MONTH))))
previous month value =  CALCULATE(sum('table'[total hours value]),previousmonth('Date'[Date]))

diff = [MTD Sales]-[last MTD Sales]
diff % = divide([MTD Sales]-[last MTD Sales],[last MTD Sales])``````

To get the best out of the weather intelligence feature. Make sure that you have a date calendar and that it has been marked as the date in the model view. Also, join her with the date column of her made/s. See:
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

See if my Time Intelligence webinar can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-Y...

Proud to be a Super User!

Highlighted
Frequent Visitor

@amitchandak Hi and thanks for asnwer me, your code was right and works. But i have another question if you dont mind. Can i make this measure called "difference" become a colum? because i dont need variation with other things. Is this possible?

Announcements

#### Power Platform Community Conference

Check out the on demand sessions that are available now!

#### Microsoft Power Platform Communities

Check out the Winners!

#### Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors