Forum Discussion
Identify differences based on date
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!
6 Replies
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=145sThanks 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!
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
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.
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
