Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Calculate by selected date

Hello all,

 

I have this model.  F..., E... and C... tables are linked to T.... by Date fields.

 

C can have just 1 order (no_commande in red), F may have several partial invoices for the same order in different dates (no_commande.2 in red),

E may have several partial deliverys for the same order in different dates.

 

I need to now for each order how much has been delivered and not invoiced yet until Date_ selected (measure based in max selected date for T.[Date]  from a slicer ) I made a calculated colums in C  ( in bleu) with the following formula with real tables names:  

 

Expedie_non_fact =

var t =

     FILTER(F; F[no_commande.2]=C[no_commande] && F[date_facture]<=[Date_Selected])

return

var v =

     FILTER(E; E[Commande]=C[no_commande] && E[DATE_DELIVERY]<=[Date_Selected])

return

sumx(v;E[Montant])-SUMX(t;F[Mont_fact])

 

*** My problem is when I choose  [Date_selected] it does not really change my value in my Expedie_non_fact column. 

I tried to do the same formula but using a measure but it does not show me this column to build my filter  F[no_commande.2]=C[no_commande], it shows me just measures.

 

I would to know, by order ( C.[no_commande] )  how much has been shipped ( E.[Montant] ) but not invoiced (F.[Mont_fact]) ultil [Date_selected] and put all this in the same matrix this way

 

 

Where

           Expédié Comm = C[date_expedi]

           No Commande = C[no_commande]

           Expédié Interal = C[Expedie_non_facture]    (calculated column)

          

Thanks for your help

4 Replies