Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi Everyone,
I have a table which consists of the channel of sales, transaction date, sales amount, the cost of the goods sold, and the sales commission.
Channel | Transaction Date | Sales Amount | Cost of Goods | Sales Commission |
A | 07-Dec-16 | 950 | 374 | 150 |
B | 18-Feb-16 | 806 | 206 | 281 |
C | 28-Jan-17 | 907 | 348 | 251 |
A | 04-Aug-16 | 880 | 158 | 258 |
B | 24-Jul-16 | 408 | 153 | 85 |
C | 31-Aug-16 | 972 | 228 | 204 |
A | 25-Jul-16 | 476 | 69 | 132 |
C | 12-Dec-16 | 216 | 115 | 37 |
B | 01-Apr-16 | 946 | 248 | 281 |
A | 20-Dec-16 | 744 | 153 | 300 |
C | 17-Dec-16 | 778 | 181 | 207 |
A | 07-Nov-16 | 467 | 121 | 164 |
A | 22-Jan-17 | 960 | 465 | 247 |
B | 05-Jan-17 | 253 | 141 | 58 |
A | 08-Aug-16 | 975 | 157 | 132 |
A | 24-Mar-17 | 909 | 100 | 349 |
A | 07-Mar-16 | 864 | 258 | 196 |
C | 26-Feb-16 | 772 | 450 | 135 |
B | 21-Dec-16 | 768 | 359 | 108 |
A | 20-Jun-16 | 827 | 332 | 210 |
C | 04-Apr-16 | 449 | 265 | 100 |
A | 30-Jan-16 | 285 | 33 | 107 |
B | 15-Apr-17 | 765 | 417 | 178 |
A | 21-Mar-17 | 565 | 81 | 255 |
C | 12-Feb-17 | 720 | 203 | 284 |
A | 15-Sep-16 | 941 | 400 | 147 |
B | 12-Dec-16 | 764 | 307 | 161 |
A | 26-Feb-16 | 639 | 338 | 70 |
C | 14-Nov-16 | 136 | 23 | 14 |
Every beginning of the month, I will take the previous 12 months data and calculate the total sales, the percentage of goods sold from total sales, the percentage of commission from total sales.
So for January 2017:
Total Sales= Calculate(SUM([Sales Amount]),FILTER(Table1,AND([Transaction Date]<01/01/2017,[Transaction Date]>=01/01/2016)))
Of course this will do if I just want to calculate the total sales amount only for january.
However I dont know what should I do if I want to change the valuation month:
example for Feb 2017:
Total Sales= Calculate(SUM([Sales Amount]),FILTER(Table1,AND([Transaction Date]<01/02/2017,[Transaction Date]>=01/02/2016)))
How can I produce some table like this? where the formula is based on the valuation month:
BOM | Jan-17 | Feb-17 | Mar-17 | ||||||
Channel | Total Sales | % Cost of Good | % Commission | Total Sales | % Cost of Good | % Commission | Total Sales | % Cost of Good | % Commission |
A | |||||||||
B | |||||||||
C |
Thank you so much for helping.
Tagging the pro user: @MattAllington, @kcantor, @Datatouille, @MiguelMartinez, @elliotdixon
Hi @ngadiez,
Please refer below stpes to get desired output
1. Clearly you will need one dim-table for data. You can use CalendarAuto function in New table to create that
2. Create Custom Column for Month
Month = EOMONTH(Table1[Transaction Date],-1)+1
3. Create a measure for Total Sales
TotalSales = CALCULATE(SUM(Table1[Sales Amount]),DATESBETWEEN('calendar'[Date],PREVIOUSYEAR(FIRSTNONBLANK(Table1[month],TRUE())),FIRSTNONBLANK(Table1[month],TRUE())-1))
4. Now pull Month in column and TotalSales in values in Matrix
Hope this helps 🙂
Hi @ngadiez,
Have you achieved your desired result? If yes, would you please kindly mark the helpful reply as an answer so that others having similar requirement can benefit from it?
Regards,
Yuliana Gu
My solution will not be elegant like Matt's but we have a different data culture at my company. Every time I provide data, I have to provide very specific filter context. That means I have to display the dates I use to make it very Fisher Price for consumers. I have a report similar to what you are building. I list our reps and their Moving Annual Net Invoiced total in a table. Beside that, I have a multi card that shows the dates used in the calculation. That reduces time spent responding to questions that I shouldn't have to answer because "Moving Annual Total" is a very specific title . . . but I digress.
That means I use extra steps that may be be useful for you depending on your company's data culture. First, as everyone else has said, make sure you have a good solid date table.
My next step is the basic calculations such as creating the Net Invoiced total. I will not walk you through that. I am sure you can handle the Sums.
Then I have a measure called Last day of previous month for simplicity and to make sure nobody misunderstands.
Last day of previous month: =
EOMONTH(TODAY(), -1)
Then a measure for the first day included in the calculation.
First date in period: =
EOMONTH ( TODAY (), -13 ) + 1
Finally, I have my moving annual net invoiced that only uses full months and ignores current month to date.
Annual Net Invoiced: =
CALCULATE (
[Net Invoiced],
DATESBETWEEN (
DimDate[DateKey],
[First date in period],
[Last day of previous month]
)
)
As I said, my company needs the extra information displayed. Yours may not.
Anyway, hope this helps and who knows, @MattAllington or @Datatouille may find an error in my calculation I have not seen as I am no where near their level in this DAX thing.
Proud to be a Super User!
Hi @ngadiez
You are doing Time-Intelligence calculations so you need a proper calendar table.
Once you have this Calendar Table, link it to your Main Table with the Dates Column and create the following measure:
SalesRolling = Calculate ( [Total Sales] , DatesInPeriod(Calendar[Dates], min(Calendar[Dates]) - 1, -1, YEAR)
where [Total Sales] = Sum(YourTable[SalesAmount])
In this example, DatesInPeriod starts from the minimum date in your current filter context, and shifts from 1 year using the Calendar table.
You can apply the same logic to the other measures.
First you need a calendar table. Ready my article here https://exceleratorbi.com.au/power-pivot-calendar-tables/
make sure you have a MonthID column that co sisters of Integers counting unique months.
Then write a custom time intelligence function. Read my article here https://exceleratorbi.com.au/dax-time-intelligence-beginners/
Something like this
Total Sales Year MAT =
CALCULATE (
[Total Sales],
FILTER (
ALL ( Calendar ),
Calendar[MonthID] >= MAX ( Calendar[MonthID] ) - 11
&& Calendar[MonthID] <= MAX ( Calendar[MonthID] )
)
)
I have a set of data:
Channel | Transaction Date | Sales Amount | Cost of Goods | Sales Commission |
A | 07-Dec-16 | 950 | 374 | 150 |
B | 18-Feb-16 | 806 | 206 | 281 |
C | 28-Jan-17 | 907 | 348 | 251 |
A | 04-Aug-16 | 880 | 158 | 258 |
B | 24-Jul-16 | 408 | 153 | 85 |
C | 31-Aug-16 | 972 | 228 | 204 |
A | 25-Jul-16 | 476 | 69 | 132 |
C | 12-Dec-16 | 216 | 115 | 37 |
B | 01-Apr-16 | 946 | 248 | 281 |
A | 20-Dec-16 | 744 | 153 | 300 |
C | 17-Dec-16 | 778 | 181 | 207 |
A | 07-Nov-16 | 467 | 121 | 164 |
A | 22-Jan-17 | 960 | 465 | 247 |
B | 05-Jan-17 | 253 | 141 | 58 |
A | 08-Aug-16 | 975 | 157 | 132 |
A | 24-Mar-17 | 909 | 100 | 349 |
A | 07-Mar-16 | 864 | 258 | 196 |
C | 26-Feb-16 | 772 | 450 | 135 |
B | 21-Dec-16 | 768 | 359 | 108 |
A | 20-Jun-16 | 827 | 332 | 210 |
C | 04-Apr-16 | 449 | 265 | 100 |
A | 30-Jan-16 | 285 | 33 | 107 |
B | 15-Apr-17 | 765 | 417 | 178 |
A | 21-Mar-17 | 565 | 81 | 255 |
C | 12-Feb-17 | 720 | 203 | 284 |
A | 15-Sep-16 | 941 | 400 | 147 |
B | 12-Dec-16 | 764 | 307 | 161 |
A | 26-Feb-16 | 639 | 338 | 70 |
C | 14-Nov-16 | 136 | 23 | 14 |
A | 20-Apr-17 | 256 | 105 | 71 |
A | 23-Aug-16 | 770 | 244 | 307 |
B | 22-Apr-16 | 499 | 63 | 60 |
B | 12-Jun-16 | 864 | 422 | 139 |
A | 16-Jun-16 | 179 | 107 | 20 |
C | 03-Jul-16 | 381 | 181 | 55 |
A | 21-Oct-16 | 557 | 201 | 150 |
B | 05-Mar-17 | 529 | 71 | 132 |
A | 01-Mar-17 | 880 | 476 | 224 |
C | 23-Jun-16 | 448 | 131 | 65 |
A | 16-Nov-16 | 192 | 115 | 25 |
B | 07-Jun-16 | 136 | 30 | 21 |
A | 17-Jan-16 | 854 | 109 | 417 |
Where I want to make Matrix like this:
BOM | Jan-17 | Feb-17 | Mar-17 | Apr-17 | ||||||||
Channel | Total Sales | % Cost of Good | % Commission | Total Sales | % Cost of Good | % Commission | Total Sales | % Cost of Good | % Commission | Total Sales | % Cost of Good | % Commission |
A | 10600 | 29.90% | 26.27% | 10421 | 33.51% | 24.07% | 9782 | 32.24% | 24.92% | 11272 | 31.52% | 27.24% |
B | 5191 | 34.44% | 21.88% | 5444 | 35.43% | 21.93% | 4638 | 37.15% | 19.69% | 5167 | 34.72% | 20.22% |
C | 4152 | 37.91% | 19.68% | 5059 | 37.99% | 21.11% | 5007 | 33.45% | 24.31% | 5007 | 33.45% | 24.31% |
For example, for Jan 17, Total sales is all transaction from 1 jan 2016 to 31 dec 2016.
for every beginning of the month (BOM), I will calculate the number from the previous 12 months.
I know how to calculate each of the items if there is no matrix column, by using calculate and filter.
However, if there is matrix column, it will become more difficult.
Is there any way to do this?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
96 | |
77 | |
63 | |
55 |
User | Count |
---|---|
143 | |
109 | |
89 | |
84 | |
66 |