DAX expression. HELP

[inti]

Hi everyone, i'm new at DAX and I need to solve the following:

1. I have a table with a receipt date "tb1"

2. I have a measure that tells me the average time my client pays "ppc"

What I want to calculate is the date i'll be payed by my clients acording to the measure based on receipt date

For example, the client1 pays around 30 days after the receipt is send, the new column should sum the date on the receipt plus the average time in the measure.

Thank you in advance!!

[inti]

Thank you very much for all the help provided. I have found a solution. 

[Anonymous]

Hi @inti 

Can you  provide detailed sample pbix file and the results you expect.So that I can help you better. Please remove any sensitive data in advance.

Best Regards,

Jayleny

[inti]

Yes, the expression is great! But now i'm learning that there is a circular reference between "ppc" and the date column "tb1"... actually the "ppc" measure uses the same column to calculate the interger output.

How can i ad a filter or solution to this?

[Anonymous]

Could you give me the DAX for the "ppc" measure? You might be able to use other data/other columns to achieve your goal.

[inti]

Yes, of course:

PPC = CALCULATE(
		DIVIDE(SUM(IMPUTACION_VTA[NUMERAL]),SUM(IMPUTACION_VTA[IMPORT_CAN]),0),
		CLIENTES[AREA]<>"M",
		CLIENTES[AREA]<>"L",
		CLIENTES[AREA]<>"V"
		) 

I already tried to encapsulate the "PPC" measure declaring a variable and changing the table to extract the date ("imputacion_vta" for "saldo_cta_cte hist") but no success

Payment forecast = 
VAR ppc_valor = [PPC]
RETURN
CALCULATE(
    ppc_valor,
    FILTER(
        'SALDO_CTA_CTE HIST',
        'SALDO_CTA_CTE HIST'[FECHA_EMI] = DATEADD('SALDO_CTA_CTE HIST'[FECHA_EMI],ppc_valor,DAY)))

Aditional info:

I'm creating this column in the "Imputacion_Vta" table, ¿Can this be the problem?

The table that reflect our ERP data is "Doc_Vta_Cab"

The table is used to create many calculated columns is "Imputacion_Vta"

PPC is a measure

Relations between tables:

"Imputacion_Vta" many to one with "Doc_Vta_Cab",

Cross filter direction: both, 

Relation active checkbox: true

Thank you in advance!

[Anonymous · Accepted solution]

Hi Inti,

Sounds like you want to add days to a specified date. For this purpose you could use the 'DATEADD' function.

To help you, I'm making these assumptions:
1) "tb1" is a column in the format dd-mm-yyy.
2) "ppc" is an integer, representating a number of days.

3) The result is a new column, which adds the value of "ppc" to the corresponding date in "tb1"

In the table containing your "tb1" column, create a new column in the "Home" tab. The formula for this new column would be:

newcolumn = DATEADD('Tablename'[tb1], [ppc], DAY)

Hopefully this solves your question. Please mark this answer as a solution if it does!