Table dynamic filter complex

[Sylvine_Wyz]

Hello EveryBody,

I have a complex request that I can't resolve.
Can you help me please ? 

I give you an extract of my data set.
I would like to have a solution that allows the calculated column Group_recap_Date to evolve according to the Invoice Date chosen by the user in the report filter.
If, for example, 05-31-2024 is chosen, the calculation of the difference must not take into account all invoices received by the supplier after this date. 

Currently, i have put in the calculated column, the Date 05-31-2024, but it's not good for my request. It's just an example.

I hope to help me 😄

Group_Recap_Date =

VAR YearOrder = YEAR('Summarize'[Date de commande])

VAR SummarizeFrn =

    SUMMARIZECOLUMNS(

    'reporting supplier_invoices'[Liaison],

    FILTER('reporting supplier_invoices',

        'reporting supplier_invoices'[invoice_date] <= DATE(YearOrder,5,31)),

    "Total", SUM('reporting supplier_invoices'[total_price_ht])

    )

VAR LiaisonExist =

COUNTROWS(

    FILTER(

        SummarizeFrn,

        [Liaison] = 'Summarize'[Liaison]

    )

)

VAR Inconnu = SWITCH(

                TRUE(),

                LiaisonExist > 0,

                1,

                0

            )

VAR TotalFrn =

MAXX(

    FILTER(

        SummarizeFrn,

        [Liaison] = 'Summarize'[Liaison]),

        [Total]

)

VAR Ecart = ROUND('Summarize'[Prix Total Wyz],2) - ROUND(TotalFrn,2)

RETURN

SWITCH(

    TRUE(),

    Inconnu = 0, "Facturation non parvenue",

    Ecart = 0, "Facturation OK",

    Ecart < 0, "Avoir à recevoir",

    Ecart > 0, "Ecart de Facturation positif",

    "A déterminer"

)

[Anonymous]

Hi, @Sylvine_Wyz 

May I ask if you have gotten this issue resolved? If it is solved, please share your solution and accept it as solution, it will be helpful for other members of the community who have similar problems as yours to solve it faster.

If it is not resolved, I hope you will provide the full .pbix file via OneDrive or SharePoint. Please be careful to remove all sensitive information and we will do our best to provide ideas for your issue.

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
Best Regards,
Fen Ling,
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[lbendlin · Accepted solution]

that allows the calculated column Group_recap_Date to evolve according to the Invoice Date chosen by the user in the report filter

You cannot create calculated columns based on user filter choices.

[Sylvine_Wyz]

Hello, 
thanks for your return.
That's what I thought. Is there another way you can think of that might meet my needs ?

[lbendlin]

What is the actual business requirement?

[Sergii24]

Hi @Sylvine_Wyz, I'm not sure I fully undertood what you're asking for, however please find below the general structure for your measure:

Measure = 
VAR _MinDate = MIN( 'Table'[Date] )     //capture the minimum date for the current filter context
VAR _Result =
    CALCULATE(
        SUM( 'Table'[HSTS_OKAY] ),      //replace with your expression
        'Table'[Date] <= _MinDate       //exclude any invoices received after this date
    )

If you want more specific help, please send a sample of table(s) to work with, and the example of desired output for a specific case (with step-by-step) explanation of the logic. Sample pbix would be the best! 🙂