Trending - taking the values form the first half of the filter and comparing to the second half

DAX
1

I want to create a trend measure that looks across a date period (Slicer). Counts the total number of rows in the dataset and then compares the first half of the data to the second half. That then would be the trend. In the attached PBX you will see the work I’ve done, i fell like I’m 70% of the way there, i can’t figure out the logic to get the middle of two dates?

The data represents the number of views each report gets over the month.

eDNA Trending.pbix (24.4 KB)
EDNA Report Views.csv (10.3 KB)

2

Bumping this post for more visibility from our experts and users.

3

Hi @chad.sharpe - I have come up with this but not sure how to manage missing dates as of now.

Test Measure1 =

VAR First_Date = firstdate('EDNA Report Views'[Date_Viewed])

VAR Last_Date = LASTDATE('EDNA Report Views'[Date_Viewed])

VAR Days_Between = QUOTIENT(countrows(DATESBETWEEN('EDNA Report Views'[Date_Viewed], First_Date, Last_Date)),2)

var firstrows = CALCULATE([Total Views],DATESINPERIOD('EDNA Report Views'[Date_Viewed],First_Date,Days_Between,DAY))

var LastRows = CALCULATE([Total Views],DATESINPERIOD('EDNA Report Views'[Date_Viewed],Last_Date,-Days_Between,DAY))

Return LastRows - firstrows

eDNA Trending.pbix (25.7 KB)

Thanks
Ankit J

1 Like
4

Hello @chad.sharpe,

Did the responses above help solve your query?

If not, can you let us know where you’re stuck and what additional assistance you need?

If it did, please mark the answer as the SOLUTION by clicking the three dots beside Reply and then tick the check box beside SOLUTION

Thank you

5

Hello @chad.sharpe

We’ve noticed that no response was received from you on the post above.

Just following up if the response above help you solve your inquiry.
If it did, please mark his answer as the SOLUTION.

In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved.

6

This is perfect… exactly what I was looking for.
I love how if there are 5 values it will count the first two and last two but leave the middle day out. I wasn’t sure how I was going to address uneven days in the filter. This actually does a good job of that.

With respect to the “weekends”. No need to address or add special logic, if the reports are viewed the days are counted. if the reports are NOT viewed the days are left out. I’m perfectly good with that.

Excellent solution…

1 Like