topic Re: Add new rows to table based on date in DAX Commands and Tips

Add new rows to table based on date

Anonymous — Mon, 09 May 2022 20:26:37 GMT

Hi all,

I want to annotations to a graph however in order to make this work with my dynamic date table (month, week, day), I need to add three new rows and two columns to the table below:

I want to have a new column called "visual date", a "type" column and three new rows pr date with start date of month, week and date. Should look something like this:

I'm unsure how to do this, can anyone help?

Best,

NicoM96

Re: Add new rows to table based on date

Jihwan_Kim — Tue, 10 May 2022 05:54:23 GMT

Hi,

Please check the below DAX formula and the attached pbix file.

It is for creating a new table.

I suggest having a Dim-Calendar Table like the attached pbix file, that shows ISO year column and ISO weeknumber column.

NewTable = VAR tableone = ADDCOLUMNS ( Data, "@VisualDate", Data[Date], "@Type", "Day" ) VAR tabletwo = ADDCOLUMNS ( Data, "@VisualDate", VAR _isoyear = MAXX ( FILTER ( 'Calendar', 'Calendar'[Date] = Data[Date] ), 'Calendar'[ISO Year CC] ) VAR _isoweek = MAXX ( FILTER ( 'Calendar', 'Calendar'[Date] = Data[Date] ), 'Calendar'[ISO Week CC] ) RETURN MINX ( FILTER ( 'Calendar', 'Calendar'[ISO Year CC] = _isoyear && 'Calendar'[ISO Week CC] = _isoweek ), 'Calendar'[Date] ), "@Type", "Week" ) VAR tablethree = ADDCOLUMNS ( Data, "@VisualDate", EOMONTH ( Data[Date], -1 ) + 1, "@Type", "Month" ) RETURN UNION ( tableone, tabletwo, tablethree )

Re: Add new rows to table based on date

Anonymous — Thu, 12 May 2022 03:14:45 GMT

Hi Anonymous

You need to create a table that combine [date] and [type] .

Then merage the Type table and dynamic date table in Query Editor to get a new table .

Then expand the new table and you will get a table like this :

Next you can create a [visual date] column according to [type] column .

visual date = var _week=Merge1[Date]-WEEKDAY(Merge1[Date],2)+1 var _month=EOMONTH(Merge1[Date],-1)+1 return SWITCH(TRUE(),Merge1[Type.Type]="Day",Merge1[Date],Merge1[Type.Type]="Week",_week,Merge1[Type.Type]="Month",_month)

The final result is as shown :

I have attached my pbix file , you can refer to it .

Best Regards,
Community Support Team _ Ailsa Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.