cancel
Showing results for
Did you mean:

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

## Dynamic Payment Term Simulation by Selected Dates

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]]] ),
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

Community Support

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&&

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.

Announcements

#### Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

#### Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

#### Fabric Community Update - April 2024

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

Top Solution Authors
Top Kudoed Authors