Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Add new rows to table based on date

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

 

  • Jihwan_Kim's avatar
    Jihwan_Kim
    4 years ago

    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 )

     

2 Replies

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Icon for Super User rankSuper User
    4 years ago

    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 )

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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.