How to pass Date as filters in calculate function while writing a measure.

[hemantsingh]

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??

[itayrom]

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".

[hemantsingh]

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 .

Result after using Maxdocdate measure

[Eric_Zhang]

@hemantsingh

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.