Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
isaac152
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.

ShopsYearMonthAmount
Shop12019650
Shop12019730
shop22019640
Shop220197

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
amitchandak
Super User
Super User

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

Power BI — MTD
https://medium.com/@amitchandak.1978/power-bi-mtd-questions-time-intelligence-3-5-64b0b4a4090e

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://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
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...


We appreciate your congratulations.

View solution in original post

3 REPLIES 3
amitchandak
Super User
Super User

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

Power BI — MTD
https://medium.com/@amitchandak.1978/power-bi-mtd-questions-time-intelligence-3-5-64b0b4a4090e

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://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
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...


We appreciate your congratulations.

@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?

ryan_mayu
Super User
Super User

@isaac152 

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

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.