DAX question

[anvikuttu]

Hi Team,

I have trouble transforming Table A to the Output Calculated Table .  I wanted for every category and based on Min and Max dates, i want to generate consecutive dates between Min and Max for the Category field. Is it possible to do in DAX?

I could have done in M query, but then Table A is derived using DAX and moving back to power query and rebuilding there is a tough work.

Thank you

Table A:

A	Min	Max
Apple	3/1/2023	3/5/2023
Orange	2/1/2023	2/5/2023
Banana	1/1/2023	1/5/2023

Output Calculated Table:

A	Date
Apple	3/1/2023
Apple	3/2/2023
Apple	3/3/2023
Apple	3/4/2023
Apple	3/5/2023
Orange	2/1/2023
Orange	2/2/2023
Orange	2/3/2023
Orange	2/4/2023
Orange	2/5/2023
Banana	1/1/2023
Banana	1/1/2023
Banana	1/3/2023
Banana	1/4/2023
Banana	1/5/2023

[FreemanZ]

hi @anvikuttu 

you would need a date table to help, try create one like:

Dates = CALENDAR(MIN(TableA[Min]), MAX(TableA[Max]))

then write a calculated table like:

Table2 = 
GENERATE(
    VALUES(TableA[A]),
    VAR MinDate = CALCULATE(MIN(TableA[Min]))
    VAR MaxDate = CALCULATE(MAX(TableA[MAX]))
    RETURN
        CALCULATETABLE(
            VALUES(Dates[Date]),
            Dates[Date]>=MinDate,
            Dates[Date]<=MaxDate
        )
)

it worked like:

[TomMartens · Accepted solution]

Hey @anvikuttu ,

using GENERATE and GENERATESERIES does the trick:

Table 2 = 
SELECTCOLUMNS(
    GENERATE(
        'Table'
        , var startValue = [Min]
        var endValue = [Max]
        return
        GENERATESERIES( startValue , endValue , 1 )
    )
    , "A" , [A]
    , "Date" , [Value]
)

A screenshot:

Hopefully, this provides what you are looking for.

Nevertheless, you must consider that DAX tables will not benefit from all compressions that are happening during data load/data refresh. If the table becomes large, you might encounter performance degradation when this table is used inside measures.

Regards,

Tom

[anvikuttu]

Thank you @TomMartens , I agree with your statement. In my case the data is not much.

[FreemanZ]

tried to imitating  TomMartens' solution:

Table4 = 
GENERATE(
    VALUES(TableA[A]),
    VAR MinDate = CALCULATE(MIN(TableA[Min]))
    VAR MaxDate = CALCULATE(MAX(TableA[MAX]))
    RETURN
        GENERATESERIES(MinDate, MaxDate)
)

[Ritaf1983]

Hi @anvikuttu 

You can achieve it with power query.
Please refer to attached link:
https://www.thepoweruser.com/2019/07/23/fill-dates-between-dates-with-power-bi-power-query/
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[anvikuttu]

Thank you, however as mentioned in my msg i would like to do this in DAX