Conditional DatesInBetween() to examine Past data in today's perspective.

[acamar22]

Hi all,

I'm hoping someone has tips on a problem I've been struggling with for days.

For context, here is a graph of Failure Count Based on Production Year:

Obviously the more recent Production Months will have less failures as they haven't had enough time to fail.

I want to compare my current 4 month failure count at the same time last year.

I'd also like to show this 4 month failure count at all points in time. (similar to rolling average)

This is how the data comes:

For example, If I wanted to look at the specific 4-month failure count in 4/24/2023, for failures during (12/24/2022 - 4/24/2023), I would only count a failure if the Fail Date and Production Date took place during that time.

I currently have this measure:

However, this is not cancelling the failures whose production dates fall out of the time frame.

I've made other measures, but they always compare the row to itself.

Example: Examining May as the Base Date.

Please let me know if thats clear, or if any clarification is needed

[lbendlin]

That's a typical "Period Over Period To Date"  pattern. 

Obligatory warning! SAMEPERIODLASTYEAR does NOT produce comparable results especially for number of days below 7. Weekdays WILL differ between years.

With that out of the way - Combine SAMEPERIODLASTYEAR with a "Is this date before the current date last year?" filter. You can create that filter from a column in your calendar table or via EDATE(-12)

[acamar22]

Hi @lbendlin 

Is it possible to do this using DatesinPeriod() ?

I ask because I want this to be a regular bar/line graph showing the 4 month past failures for each point date.

[lbendlin]

That is one option.  Usually there are many different ways in DAX to achieve the same thing. At the end of the day you have to balance code readability and performance.

[acamar22]

Thanks for the help @lbendlin 

I guess what I need is to find a way to filter my count based on that top date that is currently being looked at. (When I put it in a Matrix)

So it is looking back at 30 days of data. I want to relate that maximum/base failure date to each individual production date within those 30 days.

When I code it, it seems to relate the individual production date to the individual failure date within each row of those 3 months.

Any Idea how to fix this?

[lbendlin]

LASTDATE likely is not what you need.  Go with MAX([Fail Date]) instead and make it a variable outside of CALCULATE.

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...