Hi all,
As seen from the image above, I want to go a step further to display in the visual like so:
What I need to do is check from the first image where the Date_diff for a customer falls between 1 to 5 days. If that condition is true, I count that row as 1. At the end, I display the result against MonthYear.
This is what I am trying to achieve using DAX.
I anticipate your help
Thank youDays_Between 1st_and_Last_Purchase.pbix (389.8 KB)
Hi @upwardD,
So here’s what I did, you can change the condition if needed - it now checks where the Date_diff for a customer falls between 1 to 5 days. The numbers don’t line up with what you’ve depicted above, not sure if you based that on other data or another condition but this should get you started.
Cout of Date Diff Between 1 and 5 =
VAR myCust = VALUES( Sales[Customer] )
VAR vTable =
ADDCOLUMNS( myCust,
"@dDiff", IF( [Date_diff] >0 && [Date_diff] <=5, 1, 0 )
)
VAR Result = SUMX( vTable, [@dDiff] )
RETURN
Result
with this result.
I hope this is helpful.
@Melissa,
Thank you so very much, It does work. But I have an issue. My line manager wants me to tidy up these codes he thinks they may fail when put on visual. He thinks I should include the customerID in the queries as you did with Count of Date Diff in your code.
The codes are
Next_Purchase_Date =
VAR First = MIN(Sales[Order Date])
RETURN CALCULATE(MIN(Sales[Order Date]), Sales[Order Date] > First)
and
First_Purchaes_Date = MIN(Sales[Order Date])
He believes these codes should be bound by customerID. I don’t know enough at the moment to challenge his decision.
Please advise me otherwise.
Thank you
Hi @upwardD,
Filter context is key in Power BI, so you always need to determine how you want to visualize a result before you start writing DAX. Other fields brought into the chosen visual AND all external context from other visuals, slicers or the filter pane influence it’s result.
Something to think about.
Without customer context what result should this measure produce?
First_Purchaes_Date = MIN(Sales[Order Date])
and the answers is easy “it depends” - see these 4 visuals for example.
I hope this is helpful.
