Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
146 | |
110 | |
93 | |
84 | |
67 |