Dynamic Payment Term Simulation by Selected Dates

[Syndicate_Admin]

Hello!

I have a measure created in dax that doesn't work for me and I need to improve it to make it work for me or for you to tell me what I can change in the model to overcome this problem.

I'm simulating the shortest payment term I have per provider, per market, and per date. With the following formula:

Simulated Term 1 =
CALCULATE (
MIN ( Query1[DIM_REGLA_PLAZO_PAGO[DIAS_PAGO]]] ),
ALLEXCEPT ( Query1, Query1[Dim_RECEPTOR[RECEIVER]]],Query1[Dim_EMISOR[MERCADO_EMISOR]]],Query1[Dim_FECHA_VENCIMIENTO[FECHA_VENCIMIENTO]]]. [year] ),
Query1[DIM_REGLA_PLAZO_PAGO[DIAS_PAGO]]] > -1)

My problem is that that formula compares to me the minimum payment term (DIAS_PAGO) for that provider in that country FOR ALL ONE-YEAR DUE DATES and except for negative values.

What I intend to achieve is that I don't compare them in a year but in a DATE RANGE that I can choose. That is, if I choose 6 months I compare myself to 6 months and if I choose 3 days only 3 days.

Let me give you an example:

Supplier A has 12 trades: 6 ops of them at 0 days in May, 4 ops at 0 days in March and 2 ops at 30 days in April. I want to select only April in my filter and put 30, if I select all months it will mark 0 and if I put only May it will put 0. I attach the filter that I have and attach a screenshot of the columns.

How can I have a dynamic column and that with the formula of the beginning, it changes me as I select dates??

In the end, what I need as a concept is to simulate the minimum payment term to the supplier depending on the date I choose, it doesn't matter if it is 1 day, 1 month, 6 months or 2 years.

Thank you very much and best regards

[v-yangliu-msft]

Hi  @Syndicate_Admin ,

You can use Enter data to create a table to use as a slicer.

Then use the Switch() function to create a measure to determine if the selected date is within the

Flag =
var _today=TODAY()
var _6months=EOMONTH(_today,-7)
return
SWITCH(
    TRUE(),
    MAX('Slicer_Table'[Slicer])="3 days"&&MAX('Table'[Date])>=_today-3&&MAX('Table'[Date])<=_today,1,
    MAX('Slicer_Table'[Slicer])="6months"&&MAX('Table'[Date])>_6months&&MAX('Table'[Date])<=_today,1,0)

Place [Flag]in Filters, set is=1, apply filter.

Try the following measure:

Simulated Term 1
=
MINX(
    FILTER(ALLSELECTED('Query1'),
    'Query1'[DIM_REGLA_PLAZO_PAGO[DIAS_PAGO]>-1&&
    'Query1'[Dim_RECEPTOR[RECEIVER]=MAX('Query1'[Dim_RECEPTOR[RECEIVER])&&
    'Query1'[Dim_EMISOR[MERCADO_EMISOR]=MAX('Query1'[Dim_EMISOR[MERCADO_EMISOR])&&    'Query1'[Dim_FECHA_VENCIMIENTO[FECHA_VENCIMIENTO]]]=MAX('Query1'[Dim_FECHA_VENCIMIENTO[FECHA_VENCIMIENTO]),'Query1'[DIM_REGLA_PLAZO_PAGO[DIAS_PAGO])

If it doesn't meet your desired outcome, can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data. We can better understand the problem and help you.

Best Regards,

Liu Yang

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