Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Anonymous
Not applicable

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:

 

NicoM96_0-1652127259553.png

 

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:

 

NicoM96_1-1652127928145.png

 

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

 

Best,

 

NicoM96

 

1 ACCEPTED SOLUTION
Jihwan_Kim
Super User
Super User

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 )

 


If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Click here to visit my LinkedIn page

Click here to schedule a short Teams meeting to discuss your question.

View solution in original post

2 REPLIES 2
Anonymous
Not applicable

Hi @Anonymous 

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

Ailsamsft_0-1652325170850.png

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

Ailsamsft_1-1652325170855.png

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

Ailsamsft_2-1652325170859.png

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 :

Ailsamsft_3-1652325170862.png

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
Super User
Super User

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 )

 


If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Click here to visit my LinkedIn page

Click here to schedule a short Teams meeting to discuss your question.

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.