Filter obv projectnummer en laatste datum

[gjanus]

Hallo,

Ik kom er niet helemaal uit.
We hebben een projectstructuur van:
Hoofdproject - deelproject.

En op deelproject worden prognoses ingegeven per datum per post. De gegevens worden geladen in power query.


Nu kan ik in power query filteren op laatste datum maar dat kan dus afwijken per deelproject. - Weet iemand hoe ik kan filteren dat hij de laatste datum pakt per deelproject?

Uit voorbeeldschets zouden uiteindelijk dus alleen de gele velden opgeteld moeten worden.

Hoe stel ik dit in? Mag rechtstreeks via query, power pivot of een gewone pivot. Doel is dus per deelproject de laatst ingevulde waarde per post te zien.

 

[JEC.]

Gelijk in een draaitabel

 

[JEC.]

Kan ook in PQ met 1 stap

Tabel inladen en vervolgens deze plakken in de advanced editor

let
    Bron = Excel.CurrentWorkbook(){[Name="Tabel1"]}[Content],
    #"Rijen gegroepeerd" = Table.Group(Bron, {"hoofdproject", "projectnr", "post", "bedrag"}, {{"Aantal", each List.Max([prognosedatum]), type nullable datetime}})
in
    #"Rijen gegroepeerd"

 

[gjanus]

Top thanks JEC. Zal beide uitproberen.

 

[AlexCEL]

Hier niet met één stukje M-code maar met wat afzonderlijke stappen in PQ.

 

[gjanus]

Hoi JEC, met het kader op terugkijken los ik het liever in de power pivot op. Wat moet ik aan de measure toevoegen om in dit geval de bedragen per post op te tellen?

Hij laat nu wel netjes de laatste datum zien in waarden maar som van post 1 (gele velden) zou 600 moeten zijn, hij telt nu nog steeds alle bedragen op en niet alleen die van de laatste datum per deelproject.

 

[HSV]

Excel 365 formule(s).

De grootste dagen per post.

=INDEX(SORTEREN.OP(B2:E12;B2:B12;1;C2:C12;-1);VERGELIJKEN(UNIEK(B2:B12);INDEX(SORTEREN.OP(B2:C12;B2:B12;1;C2:C12;-1);;1);0);2)

De som van die dagen.

=SOM(INDEX(SORTEREN.OP(B2:E12;B2:B12;1;C2:C12;-1);VERGELIJKEN(UNIEK(B2:B12);INDEX(SORTEREN.OP(B2:C12;B2:B12;1;C2:C12;-1);;1);0);4))

Ingekort om de som te verkrijgen:

=LET(m;SORTEREN.OP(B2:E12;B2:B12;1;C2:C12;-1);r;VERGELIJKEN(UNIEK(B2:B12);INDEX(m;;1);0);SOM(INDEX(m;r;4)))

 

[miso1995]

Je zou eventueel ook nog in power query de tabel kunnen dupliceren en in het duplicaat te groeperen op deelproject, waarbij je MAX als criterium hanteert op veld datum. Vervolgens kan je in de oorspronkelijke tabel middels gegevens samenvoegen, per regel de laatste datum kunnen invoegen van het betreffende deelproject. Vervolgens voeg je een kolom toe, waarbij je met een IF statement kijkt of de datum van de regel gelijk is aan de laatste datum op die regel. Als het ja is, dan een ja en als het nee is, dan een nee. En dan kun je vervolgens filteren op ja/nee.

 

[JEC.]

Alles in ene

=LET(z;B2:E12;x;INDEX(z;;1);INDEX(SORTEREN(z;{1;2};{1;-1});VERGELIJKEN(UNIEK(x);x;0);{1\2\4}))

 

[JEC.]

Met eindtotaal, net als in een draaitabel

=LET(z;B2:E13;x;INDEX(z;;1);y;INDEX(SORTEREN(z;{1;2};{1;-1});VERGELIJKEN(UNIEK(x);x;0);{1\2\4});SUBSTITUEREN(ALS.FOUT(y;{"Totaal:"\""\"rep"});"rep";SOM(INDEX(y;REEKS(RIJEN(y)-1);3))))

 

[HSV]

Heel mooi; ik dacht dat het alleen om de som ging.
Ook de sortering met accolades i.p.v. sortering.op.

De laatste met 'totaal' wordt helaas geheel als tekst weggeschreven merk ik.
Maakt niet uit, desondanks mooi.

 

[JEC.]

Thanks!

Het was inderdaad tekst. Hier nog een kleine omweg om tot getallen te komen

=LET(z;B2:E13;x;INDEX(z;;1);y;INDEX(SORTEREN(z;{1;2};{1;-1});VERGELIJKEN(UNIEK(x);x;0);{1\2\4});ALS.FOUT(SUBSTITUEREN(ALS.FOUT(y;{""\""\"rep"});"rep";SOM(INDEX(y;REEKS(RIJEN(y)-1);3)))+0;{"Totaal:"\""}))