topic Re: Date Display for Report in DAX Commands and Tips

Date Display for Report

ctedesco3307 — Tue, 19 Oct 2021 17:19:40 GMT

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!

Re: Date Display for Report

smpa01 — Tue, 19 Oct 2021 17:35:54 GMT

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

Re: Date Display for Report

ctedesco3307 — Tue, 19 Oct 2021 18:23:41 GMT

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

Re: Date Display for Report

smpa01 — Tue, 19 Oct 2021 18:40:16 GMT

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

Re: Date Display for Report

ctedesco3307 — Tue, 19 Oct 2021 19:00:44 GMT

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.

Re: Date Display for Report

smpa01 — Tue, 19 Oct 2021 19:13:32 GMT

ctedesco3307 try this

Re: Date Display for Report

ctedesco3307 — Tue, 19 Oct 2021 19:35:02 GMT

Thank you so much!!