Adding Missing Rows - need help!

[AmandaL]

Hello,
I'm trying to create a summarized table in DAX that extracts customer info from my main data table and adds in the missing rows for months based on the last date in a different calculated month column. I'll be very happy if anyone can solve this for me.

Here is a simplified data extract, also showing where the data in the columns has come from.  The last column ("alternative") is conditional based on the flag column (shows a different date if the flag = 1) - see last row.

Source :	Orig Data	Orig Data	DAX Column	PQ Column	DAX Column
Column Name :	Company	ID	Flag	Active Month	Alternative Active Month
Data :	Company A	1234	0	Jan-21	Jan-21
	Company A	1234	0	Feb-21	Feb-21
	Company A	1234	0	Mar-21	Mar-21
	Company A	1234	0	Apr-21	Apr-21
	Company A	1234	0	May-21	May-21
	Company A	1234	0	Jun-21	Jun-21
	Company A	1234	0	Jul-21	Jul-21
	Company A	1234	1	Aug-21	Oct-21

I'm trying to insert/create the "missing" rows of September and October ie. fill in the gap between the last "active month" of August and the last "alternative active month" of October. This is so I can create visuals that will recognise the flag as being active in August,  September AND October. I've been trying ADDMISSINGITEMS with SUMMARIZEDCOLUMNS but cannot get it to give the answer I need. I'm looking to ideally update just the existing "active month" column, but if instead I need to create a third date column that actually does the job that would be fine as the data set is not enormous :

Company	ID	Flag	Active Month	Alternative Active Month	NEW Month Column
Company A	1234	0	Jan-21	Jan-21	Jan-21
Company A	1234	0	Feb-21	Feb-21	Feb-21
Company A	1234	0	Mar-21	Mar-21	Mar-21
Company A	1234	0	Apr-21	Apr-21	Apr-21
Company A	1234	0	May-21	May-21	May-21
Company A	1234	0	Jun-21	Jun-21	Jun-21
Company A	1234	0	Jul-21	Jul-21	Jul-21
Company A	1234	1	Aug-21	Oct-21	Aug-21
Company A	1234	1	Sep-21	Oct-21	Sep-21
Company A	1234	1	Oct-21	Oct-21	Oct-21

Thank-you

[AlexisOlson · Accepted solution]

You can't do this in the same table with DAX. You have to define a new one.

Assuming your month columns are date type, you can define a new calculated table like this:

NewTable= 
    GENERATE (
        Orig,
        VAR N = DATEDIFF ( Orig[Active Month], Orig[Alternative Active Month], MONTH )
        RETURN
            SELECTCOLUMNS (
                GENERATESERIES ( 0, N ),
                "New Month", EOMONTH ( Orig[Active Month], [Value] )
            )
    )