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
bvy
Helper V
Helper V

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!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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