Using DAX to sum the pending amount, based on a due date metric and date filter

[FGaspar]

Hi,

I have a Date and Sales table.

I've created a measure based on the due date and a date filtered by the user, to know the payment deadline (nº days) for our customers.

The problem is that, besides this, I want to sum the unpaid amout per each line returned by the measure above.

How can I do this using DAX?

Best regards,

Filipe Gaspar

[v-yuta-msft]

@FGaspar ,

Have you tried dax like pattern below?

Result =
SUMX ( Table, [Measure] )

Community Support Team _ Jimmy Tao

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

[JarroVGIT]

Hi @FGaspar ,

Wouldn't that be something like Measure = SUM(Sales[Amount]) in the same visual you have your previous measure?

Kind regards

Djerro123

-------------------------------

If this answered your question, please mark it as the Solution. This also helps others to find what they are looking for.

Keep those thumbs up coming! 🙂

[FGaspar]

Hi,

I've created report with a table (image attached). One of the columns (Due18_3) of this table should be the sum of the column "Amount", based on the date selected in the date filter (slicer):

It works fine if I use a fixed number, for example, sum of Amount with Antiguidade >18:

Due18_v3 = var total=CALCULATE(sum(v_GB_Detailed_Customer_Ledger_Entry_Agrupada[Amount]);filter(v_GB_Detailed_Customer_Ledger_Entry_Agrupada;[Antiguidade_fix_date]>18)) return if(hasonevalue(v_GB_Detailed_Customer_Ledger_Entry_Agrupada[Initial Entry Due Date]);if([Antiguidade_fix_date]>18;sum(v_GB_Detailed_Customer_Ledger_Entry_Agrupada[Amount]);blank());total)

Antiguidade_fix_date =

VAR Dia = MAX(v_GB_Detailed_Customer_Ledger_Entry_agrupada[Initial Entry Due Date])

RETURN

DATEDIFF(Dia;"31-12-2019";DAY)

The problem, is using the selected date in the filter to obtain the same result above.

[v-yuta-msft]

@FGaspar ,

You can use selectedvalue() to achieve the value in your slicer as below:

Due18_v3 =
VAR SelectedValue =
    SELECTEDVALUE ( Calendar[Date] )
VAR total =
    CALCULATE (
        SUM ( v_GB_Detailed_Customer_Ledger_Entry_Agrupada[Amount] );
        FILTER (
            v_GB_Detailed_Customer_Ledger_Entry_Agrupada;
            [Antiguidade_fix_date] > SelectedValue
        )
    )
RETURN
    IF (
        HASONEVALUE ( v_GB_Detailed_Customer_Ledger_Entry_Agrupada[Initial Entry Due Date] );
        IF (
            [Antiguidade_fix_date] > SelectedValue;
            SUM ( v_GB_Detailed_Customer_Ledger_Entry_Agrupada[Amount] );
            BLANK ()
        );
        total
    )

Community Support Team _ Jimmy Tao

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

[FGaspar]

@v-yuta-msft 

Your formula works accordingly - know the total due amount, within the filter.

Besides using the slicer filter, is it possible to filter by period of days (30, 60, 90 days)?

I tried to create a new measure for 30 days (Due30New), but it's not working:

Due30New =
var SelectValue =
SELECTEDVALUE(Calendario[data])
var total =
calculate(
sum(v_GB_Detailed_Customer_Ledger_Entry_Agrupada[Amount]);
filter(
v_GB_Detailed_Customer_Ledger_Entry_Agrupada;
[Antiguidade]>SelectValue && [Antiguidade]<=30
)
)
return
if(
hasonevalue(v_GB_Detailed_Customer_Ledger_Entry_Agrupada[Initial Entry Due Date]);
if(
[Antiguidade]>SelectValue && [Antiguidade]<=30;
sum(v_GB_Detailed_Customer_Ledger_Entry_Agrupada[Amount]);
blank()
);
total
)

The value is right at the line level, but the total is wrong.

Aditional note: I've created a new measure (Selected Value) to see the values of the VAR "SelectedValue", but I don't get any results:

Selected Value = SELECTEDVALUE ( Calendario[data] )

[v-yuta-msft · Accepted solution]

@FGaspar ,

You can create a period number column. Give the first 30/60/90 days a flag "1", give the second 30/60/90 days a flag "2" and so on. Then create a slicer based on the period column.

Community Support Team _ Jimmy Tao

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

[FGaspar]

@v-yuta-msft 

If I create a period number column, how can I update the corresponding flag, based on the on the slicer selected date?

The slicer date filter the base for both results: 

1. Due values - line and total (already solved)
2. Due values per period (30/60/90).