Forum Discussion

inti's avatar
inti
Regular Visitor
2 years ago
Solved

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...
Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    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!

     

     

inti's avatar
inti
Regular Visitor
2 years ago

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's avatar
    Anonymous
    Not applicable
    2 years ago

    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's avatar
      inti
      Regular Visitor
      2 years ago

      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!