topic Re: Advanced Date Filtering using DAX in DAX Commands and Tips

Advanced Date Filtering using DAX

Anonymous — Tue, 28 Sep 2021 09:45:24 GMT

Good day,

I have data quesry consists of number of Execel files.

Query has colunms "SHIP NAME" and "Date of Reports". Some of the "SHIP NAME" repeats various times with different "DATES". While ago I was interested to filter out only latest date lines for visuals and used following measures and custom colums:

1) Max Date = calculate(max('RNA Date of Report'[Date of Report].[Date]), filter('RNA Date of Report', 'RNA Date of Report'[Ships Name]=EARLIER('RNA Date of Report'[Ships Name])))

2) IsLatest = If('RNA Date of Report'[Date of Report].[Date]='RNA Date of Report'[Max Date].[Date],"Latest Audit", "All Audits")

Since there are much more lines in the quesry for the same "SHIPS NAME" - I'm looking for possibility to create filter for previous to last date. Some have 2 lines, but some have 3+ (dates). Any ideas?

Thanks

Aleks

Re: Advanced Date Filtering using DAX

VahidDM — Tue, 28 Sep 2021 12:28:06 GMT

Hi Anonymous

Can you add a sample of your data and result here?

For the Max Date measure, you can use LASTDATE ('RNA Date of Report'[Date of Report])
Max Date=LASTDATE ('RNA Date of Report'[Date of Report])

and then if you want to use that in the other measure, you can add a variable in your measure like this:

IsLatest = VAR _MaxDate = CALCULATE ( LASTDATE ( 'RNA Date of Report'[Date of Report] ), ALLEXCEPT ( 'RNA Date of Report', 'RNA Date of Report'[Ships Name] ) ) RETURN IF ( 'RNA Date of Report'[Date of Report] = _MaxDate, "Latest Audit", "All Audits" )

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.

Appreciate your Kudos✌️!!

Re: Advanced Date Filtering using DAX

Anonymous — Wed, 29 Sep 2021 08:54:12 GMT

Good day,

Thnaks for reply. What I'm looking for is the new measure which will filter out date previous to last. The above one is not helping me, or I;m doing something wrong.

Kidn Regards

Aleks

Re: Advanced Date Filtering using DAX

Anonymous — Mon, 04 Oct 2021 09:37:06 GMT

Raally looking for solution how to use DAX function to recognize LATEST, Previous Date for each line.

I use below functions to recognize latest, but cannot progress more ("all audit"s need to be broken down on more conditions)

1) Max Date = calculate(max('RNA Date of Report'[Date of Report].[Date]), filter('RNA Date of Report', 'RNA Date of Report'[Ships Name]=EARLIER('RNA Date of Report'[Ships Name])))

2) IsLatest = If('RNA Date of Report'[Date of Report].[Date]='RNA Date of Report'[Max Date].[Date],"Latest Audit", "All Audits")

Any more ideas?

THnaks

Aleks

#help needed

Re: Advanced Date Filtering using DAX

smpa01 — Mon, 04 Oct 2021 15:39:38 GMT

Anonymous can you provide sample data

Re: Advanced Date Filtering using DAX

Anonymous — Wed, 06 Oct 2021 12:49:32 GMT