cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
bvy
Helper III
Helper III

Need help altering DAX to look just at last 30 days...

Suppose I have two tables, Widgets and Defects. They are related on WidgetID (one to many) as shown. 

 

Widgets

WidgetIDSerialNoProductName
1100Alpha
2101Alpha
3102Alpha
4500Beta
5501Beta
6502Beta
7503Beta
8504Beta
9990Delta
10991Delta

 

 

Defects

DefectIDWidgetIDInspectionDateDefectType
10011/1/2021Peeling
10111/1/2021Peeling
10211/1/2021Peeling
10311/3/2021Cracking
10421/1/2021Peeling
10521/1/2021Peeling
10631/1/2021Chipping
10731/1/2021Cracking

 

I can easily write a DAX measure to give me DefectsPerWidget = DIVIDE(COUNT('Defects'[DefectID]), DISTINCTCOUNT('Defects'[WidgetID])). 

 

Now suppose I wanted to get the DefectsPerWidget from just the last 30 days? How could I alter the DAX to do that? Ultimately I want to report on it by ProductName.

 

Similarly, is there a way to get the DefectsPerWidget of only the last 10 WidgetID's inspected? Problem is an inspection can occasionally span multiple days. If this were SQL, I would get the last 10 WidgetID's processed as SELECT WidgetID, MAX(InspectionDate) order by 2 desc. 

 

Thank you! 

7 REPLIES 7
v-stephen-msft
Community Support
Community Support

Hi @bvy ,

 

You can modify your measure like

WidgetsPerDefect = CALCULATE(DIVIDE(COUNT('Defects'[DefectID]), DISTINCTCOUNT('Defects'[WidgetID])),FILTER('Defects',[InspectionDate]>=TODAY()-30))

 

15.png

If you want to filter WidgetID, you can create a filter and put in WidgetID.

14.png

Best Regards,

Stephen Tao

 

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

 

Greg_Deckler
Super User
Super User

@bvy Maybe:

DIVIDE(COUNTROWS(FILTER('Defects',[InspectionDate]>=TODAY()-30)), DISTINCTCOUNT('Defects'[WidgetID]))

@ me in replies or I'll lose your thread!!!
Check out my External Tool for Power BI Desktop! Microsoft Hates Greg's Quick Measures
YouTube Channel! Microsoft Hates Greg
Check out my latest book!

@v-stephen-msft , @Greg_Deckler 

Thank you both. This gets me close, but supposing I want a "categorical" 30 days? In other words, inspections aren't run on weekends and don't happen every single weekday. So supposing I want the last 30 days of just days that have activity? I feel like it might involve a table variable or some such... 

Hi @bvy ,

 

Is the date in column InspectionDate an active date?

3.png

 

 

 

Best Regards,

Stephen Tao

 

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

@v-stephen-msft Not sure what you're asking. It's a date field (no time) in the database. It doesn't change. 

Hi @bvy ,

 

Sorry, let me elaborate.

How do you define the days that have activity? You said earlier that it’s not weekends, and it’s not necessarily all working days. How can this be reflected in the data model?

8.png

 

 

Best Regards,

Stephen Tao

 

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

@v-stephen-msft Thanks for getting back to me. Suppose the ask was 5 days (just for this example). If this were SQL, I would get the "last 5 days" with select distinct top 5 InspectionDate from Defects order by InspectionDate desc

 

So if Defects has these records: 

200 1 1/1/2021 Fading
201 1 1/1/2021 Peeling
202 1 1/3/2021 Cracking
203 1 1/4/2021 Peeling
204 1 1/7/2021 Peeling
205 1 1/7/2021 Peeling
206 1 1/7/2021 Chipping
207 1 1/7/2021 Chipping
208 1 1/10/2021 Peeling
209 1 1/11/2021 Fading
210 1 1/11/2021 Fading
211 1 1/15/2021 Peeling

 

"Last 5 days" would be: 

1/15/2021

1/11/2021

1/10/2021

1/7/2021

1/4/2021

 

In this context, "last 5 days" is a bit of a misnomer, but the idea is that I want a metric around what happened over the most recent 5 (or 30) days that had activity. 

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.