User Profile
philadams
Frequent Visitor
Joined 6 years ago
User Widgets
Contributions
Re: DAX Query for working days
Do you have the option of amending the source for the date table? I implemented this in MySQL using the following in the view - NOT( WEEKDAY(dat.date) IN (5,6) OR hols.date IS NOT NULL ) AS 'Is Working Day', 'hols' is just a table containing dates for any holidays the buiness will be shut for. This way you can then simply sum this field for any filtered range on your date table.750Views0likes0CommentsCustomers Accounting for 80% of Sales (Working, but looks inefficient)
Hi, I have set up a few DAX queries based around the amount of customers accounting for 80% of our sales when looking at products (although the formula will work on any related segmentation) - var t1 = SUMMARIZE( 'Customer Transactions - Invoices', Customers[Customer Name], "Total Sales", [Total Invoiced Value (Net)] ) var t2 = ADDCOLUMNS(t1, "Rank", RANKX(t1, [Total Sales],,DESC,Dense) ) var t3 = ADDCOLUMNS(t2, "Cumulative Sales", SUMX(FILTER(t2, [Rank] <= EARLIER([Rank])), [Total Sales]) ) var total = MAXX(t3, [Cumulative Sales]) var t4 = ADDCOLUMNS(t3, "Cumulative %", DIVIDE([Cumulative Sales], total) ) var dividing_rank = MINX(FILTER(t4, [Cumulative %] >= 0.80), [Rank]) return SUMX(FILTER(t4, [Rank] <= dividing_rank),1) To Summarize - (The [Total Invoiced Value (Net)] measure is in the Invoices table, hence using it for the SUMMARIZE) t1 = Customers with total sales t2 = add Rank by total sales t3 = add cumulative Total Sales by Rank total = sum of total sales across t3 t4 = add cumulative % of total dividing_rank = finds rank of customer that straddles over 80% boundary of Cumulative % return = Count of rows up to and including all the dividing_rank customer Now this works fine, if I add it to a table with products on rows it correctly counts the number of customers accounting for 80% of sales, even with multiple product hierarchy levels. I've spent a few hours looking at alternative techniques (i.e. SUMMARIZECOLUMNS) as this just feels a bit overly complex for what I suspect is a reaosnably common pattern. I'm also wondering whether there is any way of splitting this into multiple measures as I have a few based around this same measure excluding the return (i.e. the amount of customers accounting for 20% of sales). It may be that this is about right and it does just need duplicating for similar measures.443Views0likes1CommentRe: Value of orders shipping this period, created last working day
Apologies, I should have put a better example of what I was expecting to be returned. The date filters that i'm using in the date table (Is Last Working Day etc.) are recalculated each day so are always relative to todays date. If todays date was 15/11/22 (is last working day would be true for the 14/11/22, current period would be true for all dates in todays period) I want to return a value of 10000, as there was is an order on the last working day that ships in the current period If todays date was 16/11/22 (is last working day would be true for the 15/11/22, current period would be true for all dates in todays period) I want to return a value of 0, as there was an order placed on the last working day but it does not ship in the current period. I'm assuming there is a pattern for this as the date filters could be almost anything, i.e. order value shipping next month, but created this month, for example. EDIT: Fundamentally, what i'm trying to achieve is filtering a table by both an active relationship (order_date) and an inactive relationship (estimated_ship_date), using different fields from the same related table. Other ways i've though about it is creating a table with all orders created yesterday, then all orders with a ship date in this period and doing an intersect, but this feels more complex than it needs to (accepting that it might not be).846Views0likes2CommentsValue of orders shipping this period, created last working day
Hi, I have the following tables and am trying to calculate the value of orders created on the last working day that are shipping in the current period. Or, which is how i'm currently trying to calculate it, the value of orders shipping in this period that were created on the last working day. Customer Orders Customer Order Order Date Estimated Ship Date Value 1 14/11/22 21/11/22 10000 2 15/11/22 15/12/22 15000 Dates Dates Is Current Period Is Last Working Day 14/11/22 TRUE TRUE ... ... ... 15/12/22 FALSE FALSE I have an active relationship between Order Date and the dates table, and an inactive one between Estimated Ship Date and the dates table. Building in steps i've created the following; Total Ordered Value = SUM('Customer Orders'[Value]) Order Value by Ship Date = CALCULATE ( [Total Ordered Value], USERELATIONSHIP ( 'Customer Orders'[Estimated Ship Date], Dates[Date] ) ) Order Value Shipping This Period = CALCULATE ( [Order Value by Ship Date], FILTER ( Dates, Dates[Is Current Period] = TRUE ) ) These all work correctly, but where i'm stuck is creating the final measure that takes the last one above but filters it using the Order Date that has a related date record where 'Is Last Working Day' = TRUE. I've tried a few variations but I think i'm coming unstuck with how to implement the pattern that uses a different relationship on the final filter. It could be that i've started on the wrong foot so any pointers on this are appreciated.Solved891Views0likes4Comments
Data Privacy
Microsoft Fabric Community and Privacy
To learn more about how we manage your data, please review the Microsoft Fabric Community Data Privacy guide.