Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi There,
I am trying to create a measure in which I am trying to find the MAX date from a docdate column of a sql table i.e sales-transaction table between 2 specific date ranges. Basically,the table has sales transaction of all the distributors & their corresponding retailers. I need to find the distinct distributor & retailer codes based on the last transaction date or highest transaction date between specified dates. I tried creating measure but got lost while applying filters for date. Moreover , I am trying to replicate between function of sql in dax.
Here is what i tried.
Maxdocdate= calculate(max(docdate),docdate>=01-04-2016,docdate<=31-05-2016)
can anybody enlighten me with some fruitful solution??
If I understood your case correctly, then the following expression should do the trick-
Maxdocdate = CALCULATE(MAX([docdate]), FILTER(ALL(DocdatesTable), [docdate] >= DATE(2016, 4, 1) && [docdate] <= DATE(2016, 5, 31)))
Which basically means -
"Calculate the max docdate of the original(unfiltered) DocdatesTable filtered to contain only rows where docdate >= 2016-4-1 and docdate <= 2016-5-31".
Hi Itayrom,
Thanks for suggesting a way out. I tried your solution but it is not working. As soon as i am adding the maxdocdate measure in third column after distributorcode,retailercode the value which i am getting is the max docdate for the whole month whereas i need max docdate of each unique disty-retailer combination as per its last transaction. Pleae find below a screenshot of the result.
For your information, I have arround 550 distributor & arround 1 lakh retailers associated to them. So all in all i may have somehwhere in between 60000-80000 rows for maxdocdate column to fill for a given date filter. Kindly advice .
If I understand correctly, the measure in your original post should work, just to fix the syntax error.
Maxdocdate = calculate(max(transactions[docdate]),transactions[docdate]>=DATE(2016,4,1)&&transactions[docDate]<=DATE(2016,5,31))
If it doesn't work, please post some sample data and expected output. For any question, feel free to let me know.
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
118 | |
101 | |
71 | |
61 |