Identify differences based on date

[newpbiuser01]

Hello,

I have the following data that shows the sales by product for each month:

Product	Month	Sales
Pencils	November	10
Pens	December	50
Paper	January	20
Toner	February	30
Pencils	March	45
Pens	April	70
Paper	May	50
Toner	January	60
Paper	February	45
Toner	March	10
Pencils	April	10
Pens	May	5
Paper	November	5
Pens	May	10

I need to flag the differences in the sales by product to get the following: 

Product	Previous Month (April)	Current Month (May)	Difference
Pens	70	5	-55
Paper	0	50	50
Pencils	10	0	-10

How could I do this? I tried grouping the data by product and month, but I can't figure out how to identify just the products with sales in the current and previous months and also how to show the difference in the sales. 

I'd appreciate any help!

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI file from here.

Hope this helps.

[Ashish_Mathur]

Hi,

It is quite easy to solve with the Date and Time Intelligence functions but for that we also need a year column.  If you have that, then please share that column as well.

[newpbiuser01]

Hi @Ashish_Mathur , I do have the year as well. So the table would look as follows: 

Product	Month	Year	Sales
Pencils	November	2022	10
Pens	December	2022	50
Paper	January	2023	20
Toner	February	2023	30
Pencils	March	2023	45
Pens	April	2023	70
Paper	May	2023	50
Toner	January	2023	60
Paper	February	2023	45
Toner	March	2023	10
Pencils	April	2023	10
Pens	May	2023	5
Paper	November	2022	5
Pens	May	2023	10

[amitchandak]

@newpbiuser01 , Create a date using month in case you do not have date

Date=  datevalue("01-"&[Month] &"2022")

Join the date of your table with date of date table(create a date table) and then use Time Intelligence 

Calendar = Addcolumns(calendar(date(2012,01,01), date(2024,12,31) ), "Month no" , month([date])
, "Year", year([date])
, "Month Year", format([date],"mmm-yyyy")
, "Month year sort", year([date])*100 + month([date])
, "Qtr Year", format([date],"yyyy-\QQ")
, "Qtr", quarter([date])
, "Month",FORMAT([Date],"mmmm")
, "Month sort", month([DAte])
, "FY Year", if( Month(_max) <7 , year(_max)-1 ,year(_max))
, "Is Today" ,if([Date]=TODAY(),"Today",[Date]&"")
,"Day of Year" , datediff(date(year([DAte]),1,1), [Date], day)+1
, "Month Type", Switch( True(),
eomonth([Date],0) = eomonth(Today(),-1),"Last Month" ,
eomonth([Date],0)= eomonth(Today(),0),"This Month" ,
Format([Date],"MMM-YYYY") )
,"Year Type" , Switch( True(),
year([Date])= year(Today()),"This Year" ,
year([Date])= year(Today())-1,"Last Year" ,
Format([Date],"YYYY")
)
)

example measures

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

Time Intelligence, Part of learn Power BI https://youtu.be/cN8AO3_vmlY?t=27510
Time Intelligence, DATESMTD, DATESQTD, DATESYTD, Week On Week, Week Till Date, Custom Period on Period,
Custom Period till date: https://youtu.be/aU2aKbnHuWs&t=145s

[newpbiuser01]

Thanks @amitchandak, 

One more question, if instead of just the sum of sales, I had to identify the differences in a text field - so for sales in May and April in the following table, identify where I had the sales on that product:

Region	Product	Month	Year	Sales
East	Pencils	November	2022	10
West	Pens	December	2022	50
South	Paper	January	2023	20
North	Toner	February	2023	30
East	Pencils	March	2023	45
West	Pens	April	2023	70
South	Paper	May	2023	50
North	Toner	January	2023	60
East	Paper	February	2023	45
West	Toner	March	2023	10
South	Pencils	April	2023	10
North	Pens	May	2023	5
East	Paper	November	2022	5
West	Pens	May	2023	10

Result:

Product	Region Sales Last Month	Region Sales This Month
Pen	West	North, West
Pencils	South
Paper		South

How would I aggregate based on the filtering criteria? I tried the concatenate function but that doesn't work. I'd appreciate any help!

[Ahmedx]

Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.
https://1drv.ms/u/s!AiUZ0Ws7G26Rh3lMEi3e-7CPs8b6?e=iM85yK