Calculate by selected date

[PepitoTratado]

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

[v-lili6-msft]

hi  @PepitoTratado 

First, you should know that:

1. Calculation column/table not support dynamic changed based on filter or slicer.
2. Measure can be affected by filter/slicer, so you can use it to get dynamic summary result.

https://www.sqlbi.com/articles/calculated-columns-and-measures-in-dax/

Second, for your case, you need a measure and need use ALLxxx Function in it to get it.

https://www.sqlbi.com/articles/managing-all-functions-in-dax-all-allselected-allnoblankrow-allexcept...

If you still have the problem, please share a simple sample pbix file and your expected output.

Regards,

Lin

[PepitoTratado]

Hi @v-lili6-msft , thanks for your answer

I did try your suggestion but is not working

Here's my sample

https://drive.google.com/file/d/1A3gL5xun6iU5TA-x6kNf8kS0_ctCHgL2/view?usp=sharing

I'm specting to have this

Thanks for your help

[v-lili6-msft]

hi  @PepitoTratado 

I have tested on your sample file,  for your case, just need to add  a dim order table and create a dim order table, and create the relationship with 'Order','Invoiced','Shipped' table as below:

Now when create the visual, please use [Order] field from dim order table.

here is sample pbix file, please try it.

Regards,

Lin

[PepitoTratado]

Thank you very much. it works as I spected