how to get list of Dates using two existing Dates Columns using DAX?

[Anonymous]

I have two Date Columns, eff_Date and End_Date and I want to create a new column which gives all the dates between the two dates present in a row.
For eg,
eff_date                  end_date
01-08-2012             30-05-2019
01-03-2012             29-02-2012
I want to get all the dates between these two dates(01-08-2012 and 30-05-2019) and (01-03-2012 and 29-02-2012)in DAX and not in M query, as End_date is a calculated column.

[Ashish_Mathur]

Hi,

I dont think this can be done in DAX.  If you can share the logic of generating the Dates in the end_date column, then those dates can be generated in M and then all dates between eff_date and end_date can be enumerated in M.

[Anonymous]

Hello @Ashish_Mathur , 
Thanks for the prompt reply.
Here is my end_date DAX

end_date = var temp =

TOPN(

1,

FILTER (

ar_cmg_rate_Union,

ar_cmg_rate_Union[cmg] = EARLIER ( ar_cmg_rate_Union[cmg] )

&& (ar_cmg_rate_Union[eff_date]) > EARLIER( ar_cmg_rate_Union[eff_date] )

),ar_cmg_rate_Union[eff_date],DESC

)

Return

IF(MINX(temp,ar_cmg_rate_Union[eff_date])>

ar_cmg_rate_Union[eff_date],ar_cmg_rate_Union[eff_date]-1,BLANK())

[Ashish_Mathur]

Hi,

Please share the business logic in simple Engligh (rather than a formula) of how the dates in the end_date column should be generated.

[Anonymous]

@Ashish_Mathur ,
so I have a column called eff_date and the end_date's logic is as follows:
end_date is one date previous to eff_date.
for example, 
if eff_date = 18/02/2018 then end_date is 17/02/2018.

[Ashish_Mathur]

Hi,

There is something i am missing.  To just subtract a date, why are you writing a complicated TOPN formula?

[Anonymous]

@Ashish_Mathur , This is because I have another column called 'cmg', and I need to filter eff_date based on this column. 

[Ashish_Mathur]

Hi,

I am willing ot help but i need complete information.  Let's divide the problem into 2 parts - one that of generating the end_date in M and another that of enumerating the dates between start and end_date.  Let's take part 1.  My request is:

1. Share all tables required to generate the end_date column
2. Explain the business logic
3. Show the end result

The second part of the problem is easier to solve.

[AlB]

Hi @Anonymous 

Where do you want all those dates exactly? You need to explain it better, ideally through an example.

One option is a calculated colum:

NewColumn =
CONCATENATEX ( CALENDAR ( Table1[eff_date], Table1[end_date] ), [Date ], ", " )

which will list all the dates in that same row. Another option is to create a new calculated table that will list the 2-tuples (eff_date, end_date) from your initial table repeatedly, as many times as dates are between them,  and each followed by one of those dates:

NewTable =
GENERATE (
    Table1,
    CALENDAR ( CALCULATE ( Table1[eff_date] ), CALCULATE ( Table1[end_date] ) )
)

[Anonymous]

Hey @AlB , 
I want it in a column.