Set Analysis with two date fields on same table

[JoshW]

While I'm sure my problem isn't unique, I can't seem to find an answer to how to fix the issue. Currently I have a total of 3 date fields in a table.

PULLDATE - Reference of when data is pulled

GTMDate - Date given to and pulled from SAP to gather data for this particular date

PreviousDate - Date of the previous date pulled.

These are used to gather calculations based on GTMDate and a location field that is also in the table. Each location carries it's own data to be calculated. Previous date is entered because while most weekends are empty data, sometimes one of the locations places data but all others default to the last week day. For this reason I need to null out the data for those duplicates.

I've considered doing this in the load editor but not sure how to divide between each location and calculate which is why I settled for set analysis.

I'm trying to compare like this:

If(
	Match(
    Sum([ProjGTM])/Max([BillingDays]), // AVERAGE OF GTM FOR THE CURRENT DATE
    Sum({$<GTMDate = {"=$(=PreviousDate)"} >} [ProjGTM])/Max({$<GTMDate = {"=$(=PreviousDate)"} >} [BillingDays]) // SHOULD BE AVERAGE FOR GTM OF PREVIOUS DATE
    ), 0, 
    Sum([ProjGTM])/Max([BillingDays])
)

I'm not very good with set analysis. Any help at all would be appreciated.

[Lauri]

I'm not clear what the root problem is. Do you need to get the data only for the most recent Friday for each location? 

[JoshW · Accepted solution]

I have to load data for all days but those with duplicates should null out.

Monday through Friday are typical business days while Saturday and Sunday are not typical. There are instances where a location may input data on a Saturday or Sunday though. Also, there are times when a location may close for a holiday sometime during the business week.

What I've opted to do for now as it appears to work well is use the Before() function on my pivot table to compare to the previous column/row and use the Above() function on my chart to compare previous data. Currently it seems to be working fine. Is this the route I should go for the end result?

[Lauri]

That sounds like a good solution, as long as your table and chart are correctly sorted. You could do a similar calculation in the load script, using Peek on a sorted table.