Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi all
I am struggling with a measure... hope you can help!
I have a database with contracts, and one with sales orders (by shipdate).
I created a measure which flags whether sales orders are higher, lower, or OK compared to the contract that month. The measure gives Blank() values for either no contract/no sales order combination. But I would also like it to give Blank() for any date beyond the end of the current month.
This last part is where I cannot manage to get it to work. The underlined part in the measure throws an error: a single value for column timestamp_dt in table date dimension cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation.
any ideas?
Contract Application = IF( AND(ISBLANK([Quantity Shipped by Actual Ship Date]),ISBLANK([ContractQty by Del/Ship From])), BLANK(), IF( (([ContractQty by Del/Ship From] - [Quantity Shipped by Actual Ship Date])/[ContractQty by Del/Ship From])>0.05, IF((Year('date_dimension'[timestamp_dt])*100+Month('date_dimension'[timestamp_dt]))>(YEAR(Today())*100+ MONTH(Today())),Blank(),"Lower"), IF( (([ContractQty by Del/Ship From] - [Quantity Shipped by Actual Ship Date])/[ContractQty by Del/Ship From])<-0.05, "Higher","OK")) )
Quantity Shipped by Actual Ship Date | ContractQty by Del/Ship From | yearmonthnumber (Aggragated) | Contract Application MTD |
23940,97 | 21043.14 | 201810 | Higher |
24804,02 | 23642.94 | 201811 | OK |
17454,86 | 20688.44 | 201812 | Lower |
24007,70 | 23047.65 | 201901 | OK |
20452,68 | 23990.67 | 201902 | Lower |
20789,25 | 24487.06 | 201903 | Lower |
2852,07 | 22460.74 | 201904 | Lower |
18994.67 | 201905 | ||
20451.6 | 201906 | ||
19995.34 | 201907 | ||
19335.41 | 201908 |
Would still love the community's input on this one! let me know in case the ask is unclear! Thanks a lot in advance:)
@aemb please read this post to get your answer quickly https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
User | Count |
---|---|
125 | |
108 | |
99 | |
63 | |
62 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |