Skip to main content
cancel
Showing results for 
Search instead 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.

Reply
Syndicate_Admin
Administrator
Administrator

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

Ignacio_garcia_0-1709227753383.png

Ignacio_garcia_2-1709228616721.png

Ignacio_garcia_3-1709228840383.png

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

1 REPLY 1
v-yangliu-msft
Community Support
Community Support

Hi  @Syndicate_Admin ,

 

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

vyangliumsft_0-1709280482027.png

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.

vyangliumsft_1-1709280482028.png

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.

Helpful resources

Announcements
Microsoft Fabric Learn Together

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

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

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