Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
PepitoTratado
Frequent Visitor

Calculate by selected date

Hello all,

 

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

 

Model.PNG

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

Table.PNG 

 

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 4
v-lili6-msft
Community Support
Community Support

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

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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

 

Model1.PNG

 

Thanks for your help

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:

8.JPG

 

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

 

here is sample pbix file, please try it.

 

Regards,

Lin

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Thank you very much. it works as I spected

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.