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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
ctedesco3307
Resolver II
Resolver II

Date Display for Report

Hi All! I need to include the weeknumbers in my report , but I don't want to display them as numbers, I want the data to look like it does in the Data Display column. Whats the best way to do that in DAX, or is there a smater way? Thanks in advance!  

 

ctedesco3307_0-1634664087125.png

 

1 ACCEPTED SOLUTION
smpa01
Super User
Super User

@ctedesco3307  you need a calndar table for this and the above fact[Date] needs to have a relationship with Calendar[Date], then you can write following measure to give you what you need

 

Measure = "from"&" "&MAX('Calendar'[First_Day_in_Week])&" "&"to"&" "&MAX('Calendar'[Last_Day_in_Week])

 

smpa01_0-1634664815278.png

 

Minimum dependency a calendar table with following structure

 

Calendar_Date First_Day_in_Week Last_Day_in_Week
2020-01-01 2019-12-29 2020-01-04
2020-01-02 2019-12-29 2020-01-04
2020-01-03 2019-12-29 2020-01-04
2020-01-04 2019-12-29 2020-01-04
2020-01-05 2020-01-05 2020-01-11
2020-01-06 2020-01-05 2020-01-11
2020-01-07 2020-01-05 2020-01-11
2020-01-08 2020-01-05 2020-01-11
2020-01-09 2020-01-05 2020-01-11
2020-01-10 2020-01-05 2020-01-11
2020-01-11 2020-01-05 2020-01-11
2020-01-12 2020-01-12 2020-01-18

 

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs

View solution in original post

6 REPLIES 6
smpa01
Super User
Super User

@ctedesco3307  try this out

 

 

TDate =
VAR _1 =
    ADDCOLUMNS (
        CALENDAR ( DATE ( 2021, 1, 1 ), DATE ( 2021, 12, 31 ) ),
        "Month", FORMAT ( [Date], "mmm YY" ),
        "MonthOrder",
            ( YEAR ( [Date] ) * 100 )
                + MONTH ( [Date] ),
        "Year", YEAR ( [Date] ),
        "Week", WEEKNUM ( [Date] ),
        "Day", FORMAT ( [Date], "ddd" ),
        "Billing Month",
            VAR DayNumber =
                WEEKDAY ( [Date], 2 )
            RETURN
                [Date] + ( 5 - DayNumber ) + 7 * ( DayNumber > 5 )
    )
VAR _2 =
    ADDCOLUMNS (
        ADDCOLUMNS (
            _1,
            "FirstDayOfTheWeek", MINX ( FILTER ( _1, [Week] = EARLIER ( [Week] ) ), [Date] )
        ),
        "LastDayOdTehWeek", MAXX ( FILTER ( _1, [Week] = EARLIER ( [Week] ) ), [Date] )
    )
RETURN
    _2

 

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs

This worked fantastic, accept for one issue that just occured to me. I need to use these values as columns in a matrix report and can't do that as measures? Is there a way to do this as a calculated column? I should have specified that initially - my fault there. Apologies. 

@ctedesco3307  try this

 

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs

Thank you so much!! 

smpa01
Super User
Super User

@ctedesco3307  you need a calndar table for this and the above fact[Date] needs to have a relationship with Calendar[Date], then you can write following measure to give you what you need

 

Measure = "from"&" "&MAX('Calendar'[First_Day_in_Week])&" "&"to"&" "&MAX('Calendar'[Last_Day_in_Week])

 

smpa01_0-1634664815278.png

 

Minimum dependency a calendar table with following structure

 

Calendar_Date First_Day_in_Week Last_Day_in_Week
2020-01-01 2019-12-29 2020-01-04
2020-01-02 2019-12-29 2020-01-04
2020-01-03 2019-12-29 2020-01-04
2020-01-04 2019-12-29 2020-01-04
2020-01-05 2020-01-05 2020-01-11
2020-01-06 2020-01-05 2020-01-11
2020-01-07 2020-01-05 2020-01-11
2020-01-08 2020-01-05 2020-01-11
2020-01-09 2020-01-05 2020-01-11
2020-01-10 2020-01-05 2020-01-11
2020-01-11 2020-01-05 2020-01-11
2020-01-12 2020-01-12 2020-01-18

 

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs

Thanks for the quick reply - my exmple was actually my date table. How do I get the first and last week columns in there? 

TDate = ADDCOLUMNS(
CALENDAR(date(2021,1,1), date(2021,12,31)),
"Month", FORMAT([Date],"mmm YY"),
"MonthOrder", (YEAR([Date])*100) + MONTH([Date]),
"Year", YEAR([Date]),
"Week", WEEKNUM([Date]),
"Day", FORMAT([Date],"ddd") ,
"Billing Month",
var DayNumber = WEEKDAY( [Date], 2 )
return
[Date]
+ ( 5 - DayNumber)
+ 7 * (DayNumber > 5
))

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

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

Top Solution Authors