Found First and LastNonBlank Date, Need to Pull Data From Those Dates

[AW31]

I am analyzing social media data from one channel and I'm looking to pull a specific follower count # from a post based on a date range. I previously was using FIRSTNONBLANK and LASTNONBLANK as if they were pulling the first and last follower count from a date range, but I recently found out that those pull first and last based on ascending/descending in a column, so it was pulling minimum and maximum followings instead.

I have a "Post" table, and "Post Date" and "FollowersAtPosting" fields within that. I've successfully created FirstNonBlankDate and LastNonBlankDate measures with the following input:

FirstNonBlankDate = FIRSTNONBLANK('Post'[Post Date], 1)

LastNonBlankDate = LASTNONBLANK('Post'[Post Date], 1)

For my example, my date range slicer is set 4/1 to 4/30, and the specific post dates generated from my FirstNonBlankDate and LastNBD measures are

4/1/2020 1:52:31 PM
4/30/2020 6:54:33 PM

So basically, there is a "FollowersAtPosting" number directly associated (in the same row) as these two dates. My question is what do I input to show the "FollowersAtPosting" numbers? Should I be using First/LastNonBlank or a completely different method? I've attached a couple of screenshots and the relationships below. Thanks!

[parry2k]

@AW31 one this I'm not sure, you want to see followers at post between those two dates?

As a best practice, add date dimension in your model and use it for and time intelligence calculations. There are many posts on how to add date dimension and below is the link to a few. Once the date dimension is added, mark it as a date table on table tools.

https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/
https://radacad.com/create-a-date-dimension-in-power-bi-in-4-steps-step-1-calendar-columns

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

[AW31]

Hi,

I am looking to see the followers at post for only those two dates (First/LastNB), then I'm going to calculate the difference between on my own, which should give me follower growth over that time period.

It's also a direct query, which makes it difficult to add things through dax in the power query, so I'm trying to accomplish it measures, etc.

[parry2k]

@AW31 I'm really not sure what you are looking for, it doesn't matter direct query or import, if you please explain the logic by putting a sample data (few rows) and expected output, it will help to provide the solution.

[AW31]

Here's an example of data in my Power Query. I have a post date filter that has brought me to this point, filtering all posts between 4/1 and 4/30. I try to pull First Following by using:

First Following =

FIRSTNONBLANK('Post'[FollowersAtPosting], 1)

The result is 530000, but I am trying to get 546545 since it is the first FollowersAtPosting # in the range.

Conversly, when I try 

Last Following =

LASTNONBLANK('Post'[FollowersAtPosting], 1)

The result is 550000, but I am trying to get 545536 since it is the last FollowersAtPosting # in the range. 

This was when I realized that first and last non blank are pulling the minimun and maximum values. So I'm not sure if there is a way to sort by a different field since the default for First/Last NonBlank is sorting ascending/descending. 

However, when I attempted to isolate the first and last NonBlank dates, I used:

FirstNonBlankDate = FIRSTNONBLANK('Post'[Post Date], 1), the result was 4/30/2020 23:54:33

LastNonBlankDate = LastNONBLANK('Post'[Post Date], 1), the result was 4/1/2020 18:52:31

I got the result I wanted for finding the right Post Dates, but not the FollowersAtPosting. I want to get the same values from the same row that I got the correct dates from, instead of the max and min values. Thanks again!

[parry2k]

@AW31 try following measures:

Starting Followers = 
VAR __minDate = MIN ( Table[PostDate] )
RETURN
CALCULATE ( MIN ( Table[Followers at Posting] ),
ALLSELECTED ( Table ),
Table[PostDate] = __minDate
)

Ending Followers = 
VAR __maxDate = MAX ( Table[PostDate] )
RETURN
CALCULATE ( MIN ( Table[Followers at Posting] ),
ALLSELECTED ( Table ),
Table[PostDate] = __maxDate
)

and from here you can calculate %.

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

[AW31]

Hmm, I tried that and I got close, no syntax errors but I get a query loading error. So somehow it is still searching through too many rows, even though my outside filters should have it only searching through a few. Any other additions/suggestions? I'll keep tinkering with it as well, I'm not as knowledgeable with VAR inputs so I'll learn up on them as well, thanks again for helping!

[parry2k]

@AW31 interesting, are you using a direct query, change ALLSELECT ( Post ) to ALLSELECT ( 'Post'[Post Date] )

[AW31]

I'm still attempting to find a solution to this. I'm basically trying to do a VLookup based on the following and date columns (telling Power BI to find the first date value, then grabbing the followercount from that row.

I attempted this but was unsuccessful, not sure why it doesn't work. I get a 1,000,000 rows error. Using the FirstNonBlakeDate measure as my search/match criteria.

First Date Following = LOOKUPVALUE('Post'[FollowersAtPosting], 'Post'[Post Date], [FirstNonBlankDate])

[parry2k]

@AW31 use datesbetween  DAX function to calculate the count between selected dates.

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

[AW31]

Hi,

I'm not looking to calculate the count between selected dates, I'm looking to pull the single follower count value associated with each of those two dates. Below I have the columns in Power Query from the "Post" table. I'm wanting to reference the "FollowersAtPosting" that is in the same row as the "Post Date." I have the 2 post dates that I want isolated already, now I just need to reference the "FollowersAtPosting" number in the same row. Does this make sense? Thanks!