Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
hemantsingh
Helper V
Helper V

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

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

3 REPLIES 3
itayrom
Resolver II
Resolver II

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 .

 

Result after using Maxdocdate measureResult after using Maxdocdate measure

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.