Alright, for the middle of the week I have a new DAX question.
I am working from a sales fact table that has a line for each line of an invoice. It has several date fields including order date, posting date, and date converted. I am needing to calculate the sales volum of quotes that converted into orders. If the quote converted, the date will be equal to or after the order date (some convert the same day). If the quote did not convert, the date will be 1/1/1753 because . . . what ever logic is behind that.
The desired end result is that I can sum up the sales ([Line Amount] where the quote converted. In addition, I will need to calculate the percentage of quotes that convert.
I am obviously making this too hard or not fully understanding dates in dax. As I am still relatively new to dax, it is probably the later. I know that @Greg_Deckler and @greggyb will find this a simple question.
You help is appreciated and, in case you haven't heard it today, you guys in this forum rock! I appreciate being able to come to this forum for help as, oddly enough, I am the current DAX guru in my office and I am still a beginner.
I got real confused when I read this at first, because I read the first line as part of the challenge itself, and I was trying to figure out how the middle of the week could be significant. I should drink more coffee.
Anyway, this is an area where it makes sense to use Power Query to add an additional field, or to add a DAX calculated column. The field will simply be a flag indicating whether a given invoice line has been converted.
This field can be defined as below:
// Power Query M // Add a custom column ConvertedFlag = [Date Converted] <> #date(1753,1,1) and [Date Converted] >= [Order Date] // OR // DAX // Calculated column ConvertedFlag = 'FactSale'[Date Converted] <> DATE(1753,1,1) && 'FactSale'[Date Converted] >= 'FactSale'[Order Date]
Either of these will provide a column made up of true and false values. When the flag field is true, then the line has been converted.
Your measure is then as simple as this:
// DAX // Measure Converted Sales = CALCULATE( SUM( 'FactSale'[Lime Amount] ) ,'FactSale'[ConvertedFlag] )
Well, my tea kicked in prior to seeing your response. I believe you created something similar to what I had cobbled together. I did a calculated column showing the number of days from order date to converted date and calculated total sales where days to convert was >= 0. I was trying to create this in a measure and was failing miserably. I am checking it for functionality now and as soon as that is complete I will give your solution a go to see which performs better.
Stay tuned to the same 'Bat-time' and the same "Bat-channel.'
My quick thought is to create a calculated column for Quotes Result ( might be useful to add it as slicer or filter ) like
Quote Result = IF( Sales[Order Date] =< Sales[Convert Date]; "Converted";"Not Convert")
note : you might need to replace the 1/1/1753 with a date after 1900 since there might be issues on date calculations..( with powerquery ).
I suppose you like to show the quotes converted on the day that convert and the quotes that didn't on the day that ordered.
Create two inactive relantionships between Sales[Order Date] and Dates[Date] ( date dimention table ) and one between Sales[Convert Date] and Dates[Date].
The two measures could be
Converted Amount = CALCULATE ( SUM ( Sales[Line Amount] ); Sales[Quote Result] = "Converted"; USERELATIONSHIP ( Sales[Convert Date]; Dates[Date] ) ) Not Converted Amount = CALCULATE ( SUM ( Sales[Line Amount] ); Sales[Quote Result] = "Not Converted"; USERELATIONSHIP ( Sales[Order Date]; Dates[Date] ) )
The percentage of quotes could be
Pct or converted quotes =
COUNTROWS ( Sales );
Sales[Quotes Result] = "Converted";
USERELATIONSHIP ( Sales[Convert Date]; Dates[Date] )
COUNTROWS ( Sales );
USERELATIONSHIP ( Sales[Order Date]; Dates[Date] )
Probably should be a faster and better solution..
@konstantinos Don't apologize! I learn so much from everyone here as everyone completes the calculations in a different manner. Your solution is being added in now. As this is already a large, slow file and you lose people after waiting 3 seconds for an answer, I am trying all of them for both accuracy and speed. This is the way I learn, trial and error to determine the 'best' solution.