Forum Discussion

ctedesco3307's avatar
ctedesco3307
Resolver II
4 years ago
Solved

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...
  • smpa01's avatar
    smpa01
    4 years ago

    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])

     

     

    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

     

smpa01's avatar
smpa01
Community Champion
4 years ago

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])

 

 

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

 

ctedesco3307's avatar
ctedesco3307
Resolver II
4 years ago

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
))