topic Re: Problem getting data from one table to another table in the range between dates in DAX Commands and Tips

Problem getting data from one table to another table in the range between dates

SEAM_98 — Thu, 21 Dec 2023 20:24:51 GMT

I have problems to create a column as inputs coming from different tables. It does not pull data from the other tables. I would like to know if you have any way to solve this problem.

Here is some information from my tables so you can understand what I am trying to do with an example.

Table 1

Table 2

I have my fact table (Table2) with dates. I want the dates that are between those ranges, to return me the "MES_CUOTA".

For example: 10/24/23 (From table 2) is between 09/26/2023 and 10/25/2023 (From table1) and in another column it should be returning October_23. So for each date in table 2.

Re: Problem getting data from one table to another table in the range between dates

Fowmy — Fri, 22 Dec 2023 06:16:15 GMT

SEAM_98

Add a calculated column to your Table 2 using the following code:

MES_CUOTA_COL = VAR __Data = table2[FECHA DE CONFIRMACION DE LA OC DELL CLIENTE] VAR __Result = CALCULATE ( MAX ( table1[MES_CUOTA ), table1[Fecha Inicio] <= __Data, table1[Fecha Fin] >= __Data ) RETURN __Result

Re: Problem getting data from one table to another table in the range between dates

SEAM_98 — Fri, 22 Dec 2023 16:18:13 GMT

Hi Fowmy

I don't know only works for the year 2023. The others previous years don't work :c. For example,

But with 2023, it works.

I tried moving the data (only the dates from my table, Mes cuota) in another spreadsheet and it worked. I understand the my table has some issues.

I check and those fields are with the correct format.

I don't know how can I fix it.

This is my table from mes_cuota

Please your help. I moved sensitive information in order to give you, but when I run the code It worked. I don't know why.

Re: Problem getting data from one table to another table in the range between dates

Fowmy — Fri, 22 Dec 2023 19:04:04 GMT

SEAM_98

Please check your data, the code should work any date as long as there is data. Please share a dummy PBI file to check

Re: Problem getting data from one table to another table in the range between dates

SEAM_98 — Fri, 22 Dec 2023 19:42:16 GMT

Hi Fowmy

I have to give you my PBI file in order to find the problem.
https://we.tl/t-4N5jk5tkEG

I am attaching link to download. Only have 7 days.

Please I look forward to your promt reply.

Re: Problem getting data from one table to another table in the range between dates

Fowmy — Fri, 22 Dec 2023 20:02:22 GMT

SEAM_98

I modifed the code with a different approach:

MES_CUOTA = VAR _data = 'FACT COTIZACIONES'[FECHA DE CONFIRMACION DE RECEPCION DE LA OC DEL CLIENTE] VAR _result = MAXX( FILTER( 'DIM MES', _data >= 'DIM MES'[Fecha Inicio] && _data <= 'DIM MES'[Fecha Fin] ), 'DIM MES'[MES_CUOTA] ) RETURN _result