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:   ...
  • 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 )

     

Jihwan_Kim's avatar
Jihwan_Kim
Super 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 )