Functional and Ugly DAX to get value at the previous date

[GoravSeth]

I've been hacking my way through the dax jungle and this calculation is working, but is not very elegant.  Looking for tips on how to make this better. I could prob switch to a measure, but starting with caculated col as its a bit easier to groc.

A team is collecting metrics on a quarterly basis, but they miss some quarters...One of the metrics is total followers, and i want to be able to calculate the difference in total followers from the previous time when they collected the info, so we can see growth / loss.

The dax, with comments on what I think I'm doing.  

CalcNewFoll_V2 = 
VAR maxDate = Facebook_Combined[Date] // date on the current row 
VAR totFol = Facebook_Combined[Total Followers ] // total followers for current row
VAR countryKey = Facebook_Combined[CountryTerritoryKey] //country key for current row
VAR prevFolTable = FILTER(Facebook_Combined,Facebook_Combined[Date]<maxDate && Facebook_Combined[CountryTerritoryKey] = countryKey && Facebook_Combined[Total Followers ] > 0  ) //create virtual table of just the values for the country that are prior to the date on the current row
VAR filterDate = MAXX(prevFolTable,Facebook_Combined[Date]) //get the most recent date in that table
VAR prevFolNumTable = FILTER(Facebook_Combined,Facebook_Combined[Date] = filterDate &&  Facebook_Combined[CountryTerritoryKey] = countryKey && Facebook_Combined[Total Followers ] > 0  ) //create virtual table of just the values where equals the previous date
VAR prevFolNum = MAXX(prevFolNumTable,Facebook_Combined[Total Followers ]) //get the previous total followers 
RETURN 
IF(totFol > 0 && prevFolNum >0,totFol - prevFolNum) //subtract

the data is coming from a bunch of files in a sharepoint folder that are being combined.  the data collection process is a bit loose, so there are blank rows and other nonsense.  sample data (filtered for just one CountryTerritoryKey - there are many).  there are other columns that have data for the rows that are blank for total followers, so i cant just eliminate blank rows.  the rows that are here need to be here (for now at least...)

Source.Name	CountryTerritoryKey	Date	Total Followers	CalcNewFoll_V2
Facebook Q424.xlsx	7	10/1/2024	12379	7
Facebook Q324.xlsx	7	7/1/2024	12372	38
Facebook Q224.xlsx	7	4/1/2024	12334	25
Facebook Q123 to Q124.xlsx	7	1/1/2024	12309	-45
Facebook Q123 to Q124.xlsx	7	10/1/2023	12354	198
Facebook Q123 to Q124.xlsx	7	7/1/2023	12156	95
Facebook Q123 to Q124.xlsx	7	1/1/2023	12061
Facebook Q424.xlsx	7	10/1/2024
Facebook Q424.xlsx	7	10/1/2024
Facebook Q324.xlsx	7	7/1/2024
Facebook Q324.xlsx	7	7/1/2024
Facebook Q224.xlsx	7	4/1/2024
Facebook Q224.xlsx	7	4/1/2024
Facebook Q123 to Q124.xlsx	7	10/1/2023
Facebook Q123 to Q124.xlsx	7	10/1/2023
Facebook Q123 to Q124.xlsx	7	1/1/2024
Facebook Q123 to Q124.xlsx	7	1/1/2024
Facebook Q123 to Q124.xlsx	7	7/1/2023
Facebook Q123 to Q124.xlsx	7	7/1/2023
Facebook Q123 to Q124.xlsx	7	4/1/2023

Your insights are appreciated!

[GoravSeth]

Thanks for the additional info.  That approach is definitely more robust.

Cheers

[FBergamaschi · Accepted solution]

Hi,

this is my version, just a bit optimized (only bold) but you had done a good job already

CalcNewFoll_V2mia =

VAR CurrDate = Facebook_Combined[Date] // date on the current row

VAR totFol = Facebook_Combined[Total Followers] // total followers for current row

VAR countryKey = Facebook_Combined[CountryTerritoryKey] //country key for current row

VAR prevFolTable = FILTER(Facebook_Combined,Facebook_Combined[Date]<CurrDate && Facebook_Combined[CountryTerritoryKey] = countryKey && Facebook_Combined[Total Followers] > 0  ) //create virtual table of just the values for the country that are prior to the date on the current row

VAR filterDate = MAXX(prevFolTable,Facebook_Combined[Date]) //get the most recent date in that table

VAR prevFolNumTable = FILTER(prevFolTable,Facebook_Combined[Date] = filterDate ) //create virtual table of just the values where equals the previous date

VAR prevFolNum = CALCULATE( SELECTEDVALUE( Facebook_Combined[Total Followers]), REMOVEFILTERS( ), Facebook_Combined[Total Followers] <> BLANK(), Facebook_Combined[Date] = filterDate, Facebook_Combined[CountryTerritoryKey] = countryKey ) //get the previous total followers

RETURN

IF(totFol > 0 && prevFolNum >0,totFol - prevFolNum) //subtract

The only non elegant part was this

VAR prevFolNum = MAXX(prevFolNumTable,Facebook_Combined[Total Followers]) but I would say that instead of not being elegant it was just risky as this assumes the nr of followers never decreases

Switching to a measure would give you the benefit of a dinmaic calculation, if you want we can discuss that

If this helped, please consider giving kudos and mark as a solution

@mein replies or I'll lose your thread

consider voting this Power BI idea

Francesco Bergamaschi

MBA, M.Eng, M.Econ, Professor of BI

[GoravSeth]

Thanks @FBergamaschi !  The updated version seems to work perfectly - and i figured it needed a calculate in there somewhere. 

The old version was able to capture decreases in total followers, bc the prevFolNumTable should only have one row because its filtering for date equals the previous date, and there should only be one row for the previous date with a value for total followers.

here is a screenshot of old and new

what felt most unelegant to me was how I approached getting the previous date value, and how many virtual tables i needed to create.   but it seemed to work.

i do plan to work on a measure for this next.  if you have any suggestions they will be appreciated.  i'll mark your reply as the solution.

cheers

[FBergamaschi]

Hello @GoravSeth 

for the measure, I suggest you create another post so we keep things tidy. I shall reply (or somebody else as good as me in DAX will)

For the MAXX thing, yes of course having a single row you could also use MINX or AVERAGEX but honestly, as a developer, I feel the duty of warning you against this as if things wo wrong (for some reason you have more than one row, like in the detaset you sent with the blank rows), you will not notice the error. Using SELECTEDVALUE, on the ocntrary, if you have more than one value, you notice it (it returns blank or the value you decide and you can trigger an error).

All the best

FB

If this helped, please consider giving kudos and mark as a solution

@me in replies or I'll lose your thread

consider voting this Power BI idea

Francesco Bergamaschi

MBA, M.Eng, M.Econ, Professor of BI