Sum last value set expression included

[blunckc1]

Hi community,

Need some help with an expression for a bar chart.  Attached is a spreadsheet with sample data for you to refer to.

Essentially, for every [Start Date] I need to calcuate:

Sum({$<[ContractsFull.ContractPeriod]={'Future'}>} [ContractsFull.ContractedFTE])

But as you can see there are multiple results per [Start Date].  So I only want the calculation to include data in the row of latest ContractsFull.SnapshotDate per [Start Date].

I tried using FirstSortedValue within an Aggr() expression but can't get it to work properly... Any ideas?

Note: [Start Date] actually comes from another table of data that joins by the ContractsFull.SnapshotDate, like a master calendar.

Thanks

Carl

[ArnadoSandoval]

Hi @blunckc1 

Your [Start Date] column is coming from the PayPeriods table which 'associates' with your Contracts table on the SnapshotDate,  eventually this association is part of the problem, suggestions:

• Try using a Mapping Load to bring the 'Start Date' to your Contracts table level, with this script:

Map_PayPeriods:
Mapping Load
ContractsFull.SnapshotDate,
"[Start Date]"
FROM [lib://Sample_Data/SampleData-FirstSortedValue.xlsx]
(ooxml, embedded labels, table is Sheet1);

Contracts:
LOAD
ContractsFull.ContractID,
ContractsFull.SnapshotDate,
ApplyMap('Map_PayPeriods', ContractsFull.SnapshotDate) As [Start Date],
ContractsFull.ContractedFTE,
ContractsFull.ContractPeriod
FROM [lib://Sample_Data/SampleData-FirstSortedValue.xlsx]
(ooxml, embedded labels, table is Sheet1);

•  Attach your real application, so I could explore your tables association.

The ApplyMap should work.

hth

[blunckc1]

Hi @ArnadoSandoval 

Yeah change at the data model layer is where I need to go... 

Thanks heaps for your help!!

Cheers

Carl