Filtering rows based on relative date when using DirectQuery

[Chaz]

I have a table coming from a SQL Server that I have connected to via DirectQuery. Column A in this table is a list of dates. I want to create a measure that gives me the sum of Column B values only for rows relating to one before the last date in Column A. Is there any way to do this?

[v-frfei-msft]

Hi @Chaz,

Based on my test, we can create a measure like this to meet your requirement.

Measure = CALCULATE(SUM(Product1[Quantity]),FILTER(ALL(Product1),Product1[SalesDate]<MAX(Product1[SalesDate])))

Regards,

Frank

[Chaz]

Hi @v-frfei-msft,

Hmm I think you may have misunderstood my question...so I needed to get the Quantity for Thursday the 10th of May according to your example. Because it is 'one before the last date', the last date being 11 May.

I am trying to implement something like this, still haven't tested if it works though:

PreviousWeek_% of C Collections = CALCULATE([% of C Collections], FILTER(DimDate, DimDate[DateOfWeekEnding] >= (TODAY() - 14) && DimDate[DateOfWeekEnding] < (TODAY() - 7)))

In this example, I am taking as my 'Date' column the date of week ending, and we have a rolling update of data so I'm trying to catch the previous week using the "TODAY() - [numDays]" part of the measure.

Please let me know if you see anything immediately wrong with it haha.

[v-frfei-msft]

Hi @Chaz,

Based on my test, we can create measures like this to meet your requirement.

yymmdd = YEAR(MAX(Product1[SalesDate]))*10000+MONTH(MAX(Product1[SalesDate]))*100+DAY(MAX(Product1[SalesDate]))

rank = IF(ISBLANK([yymmdd]),BLANK(),RANKX(ALL(Product1),[yymmdd],,ASC,Dense))

Measure = IF([rank] = MAXX(ALL(Product1),[rank]-1),MAX(Product1[Sales]),BLANK())

Here is the result as we excepted.

And for you formula as below.  I think here [% of C Collections] should be a measure, if so that should be ok. Please refer to the picture of my test.

PreviousWeek_% of C Collections = CALCULATE([% of C Collections], FILTER(DimDate, DimDate[DateOfWeekEnding] >= (TODAY() - 14) && DimDate[DateOfWeekEnding] < (TODAY() - 7)))

Regards,

Frank

[v-frfei-msft · Accepted solution]

Hi @Chaz,

Does that make sense? If so, kindly mark my answer as a solution to close the case please.

Regards,

Frank

[jbjs5820]

Hi, 

I´m facing the same problem!

This works in direct query on Pbi Desktop. But, in the Service, it allways fixes the date.

Any suggestions?

Thanks and regards