Add new rows to table based on date

[Anonymous]

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

[Anonymous]

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.

[Jihwan_Kim · Accepted solution]

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 )