Forum Discussion
DAX expression. HELP
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!!
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!
6 Replies
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!
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?
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!
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
Thank you very much for all the help provided. I have found a solution.
