Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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?
Solved! Go to Solution.
Hi @Chaz,
Does that make sense? If so, kindly mark my answer as a solution to close the case please.
Regards,
Frank
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
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.
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
Hi @Chaz,
Does that make sense? If so, kindly mark my answer as a solution to close the case please.
Regards,
Frank
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
User | Count |
---|---|
73 | |
70 | |
38 | |
23 | |
23 |
User | Count |
---|---|
96 | |
94 | |
50 | |
42 | |
40 |