Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
I'm new to DAX so bare with with me!
I've been tasked with totalling dollars and units, year over year, ignoring the first month worth of sales (viewed as stock buildup that we don't want to count). I created a measure that finds a specific licensee's initial purchase date then addes 31 days to it to get an adjusted date:
Licensee ID | Sales Amount Net | Sales Quantity Net | Year | Adjusted Date | Adjusted Sales Amount |
37550 | $21,149.02 | 1990 | 2016 | 1/14/2017 | 1990 |
37550 | $125,423.79 | 11995 | 2017 | 2/4/2017 | 11995 |
37550 | $156,864.29 | 14236 | 2018 | 2/3/2018 | 14236 |
37550 | $51,099.99 | 4699 | 2019 | 2/4/2019 | 4699 |
Year | Sales Amount Net | Sales Quantity Net |
2017 | $123,378.91 | 11803 |
2018 | $156,864.29 | 14236 |
2019 | $51,099.99 | 4699 |
Hi,
I cannot understand your question. IN your formula, you refer to a Transaction date but there is no such column in your pasted tables. Please share the dataset and expain how you arrived at the end result of quantity and sales.
HI @tyleharris,
I think you need to use allselected function on your table to break current row contents and let formula looping whole table.
Adjusted Sales Amount = VAR currDate = MIN ( 'Sales Transactions'[Transaction Date] ) RETURN CALCULATE ( SUM ( 'Sales Transactions'[Sales Quantity Net] ), FILTER ( ALLSELECTED ( 'Sales Transactions' ), 'Sales Transactions'[Transaction Date] >= currDate ) )
In addition, I'd like to suggest you add a calendar table and use calendar date as table visual fields for measure calculate.
Please understand that this link is provided with no warranties or guarantees of content changes, and confers no rights.
Regards,
Xiaoxin Sheng
Hi @v-shex-msft
Thank you for your reply. It definately has me heading in the right direction. However, it's not quite right.
I took your measure and changed it a little from a piece that was given to me from a coworker:
I receive the correct total of 31037 which is the good news, but I need it to calculate the year over year - Which it appears to not be displaying correctly:
Licensee ID | Sales Amount Net | Sales Quantity Net | Gregorian Year | Adjusted Date | Adjusted Sales Amount |
37550 | $21,149.02 | 1990 | 2016 | 1/14/2017 0:00 | 31037 |
37550 | $125,423.79 | 11995 | 2017 | 2/4/2017 0:00 | 30181 |
37550 | $156,864.29 | 14236 | 2018 | 2/3/2018 0:00 | 17608 |
37550 | $54,583.90 | 4998 | 2019 | 2/4/2019 0:00 | 3112 |
It needs to be displayed like this:
Gregorian Year | Sales Amount Net | Sales Quantity Net |
2017 | $123,378.91 | 11803 |
2018 | $156,864.29 | 14236 |
2019 | $54,583.90 | 4998 |
Total: | 31037 |
Thanks!
I think some of your attempts so far are filtering out the first 31 days each year, not just the first 31 days of the first year. Something like the following may be more what you are looking for
Adjusted Sales = SUMX( VALUES(Licensee[Licensee ID]) ,CALCULATE(SUM('Sales Transactions'[Sales Amount Net]) ,FILTER(ALL('Sales Transactions'[Transaction Date]), 'Sales Transactions'[Transaction Date] > CALCULATE( MIN('Sales Transactions'[Transaction Date]), ALL('Sales Transactions')) + 31) ) )
Hi @d_gosbell
Getting closer! I adjusted your formula to sum of Sales Quantity instead of the Sales Amount since thats what I've been working on at the moment. You are correct in that I need to filter out the first 31 days of sales of the first year.
The formula is returning the correct total of 31037, but is still displaying the yearly sums incorrectly. It looks like the yearly sums are still filtering out the first 31st days of each year.
Licensee ID | Sales Amount Net | Sales Quantity Net | Gregorian Year | Adjusted Date | Adjusted Sales |
37550 | $21,149.02 | 1990 | 2016 | 1/14/2017 0:00 | |
37550 | $125,423.79 | 11995 | 2017 | 2/4/2017 0:00 | 10947 |
37550 | $156,864.29 | 14236 | 2018 | 2/3/2018 0:00 | 12610 |
37550 | $54,583.90 | 4998 | 2019 | 2/4/2019 0:00 | 3112 |
Total | 31037 |
Yearly totals should be:
Gregorian Year | Sales Amount Net | Sales Quantity Net |
2017 | $123,378.91 | 11803 |
2018 | $156,864.29 | 14236 |
2019 | $54,583.90 | 4998 |
Total | 31037 |
Thank you!
Hi @tyleharris ,
Please take a look at following blog about deal with measure total level calculation issue :
Measure Totals, The Final Word
Regards,
Xiaoxin Sheng
Hi @v-shex-msft ,
I made some progress on my formula - It totals the yearly sale quantities correctly, but now the grand totals are off. I've narrowed down the problem, but not sure how to fix it - As the formula currently stands the grand total is the sum of both Licensee IDs quantities, but only from Licensee ID 37750 adjusted first purchase date (their first purchase date +31 days) since it's date is before Licensee ID 81706. What I need this formula to do is look at each unique Licensee ID first purchase date, add the 31 days, then start summing the quantities from that date. I have it working for the individual Licensee Yearly Totals, but as I said the grand totals are not summing correctly.
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
115 | |
112 | |
105 | |
95 | |
58 |
User | Count |
---|---|
174 | |
147 | |
136 | |
102 | |
82 |