Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Good afternoon,
I've been at an impasse for the past hour regarding a Date Table I created within a new report.
I'll share what I've attempted prior to sharing DAX & images.
I'm not sure if this would present an issue, but the data tables are pulled in through Direct Query.
Here is the DAX I utilized to make the Date Table:
Date =
GENERATE (
CALENDAR( DATE( YEAR( TODAY() ) - 2, MONTH( TODAY() ), DAY( TODAY()) ), TODAY()),
VAR startOfWeek = 1 // Where 1 is Sunday and 7 is Saturday, thus a 3 would be Tuesday
VAR currentDay = [Date]
VAR days = DAY( currentDay )
VAR months = MONTH ( currentDay )
VAR years = YEAR ( currentDay )
VAR nowYear = YEAR( TODAY() )
VAR nowMonth = MONTH( TODAY() )
VAR dayIndex = DATEDIFF( currentDay, TODAY(), DAY) * -1
VAR todayNum = WEEKDAY( TODAY() )
VAR weekIndex = INT( ROUNDDOWN( ( dayIndex + -1 * IF( todayNum + startOfWeek <= 6, todayNum + startOfWeek, todayNum + startOfWeek - 7 )) / 7, 0 ) )
RETURN ROW (
"day", days,
"month", months,
"year", years,
"day index", dayIndex,
"week index", weekIndex,
"month index", INT( (years - nowYear ) * 12 + months - nowMonth ),
"year index", INT( years - nowYear )
)
)
Here is a view from the relationship pane:
Here is the Edit Relationship View:
I appreciate any help in resolving this issue!
Solved! Go to Solution.
Is there any reason why you should keep dateadded and Date columns in Date/Time datatype instead of Date(mm/dd/yyyy format)?
Try it and let us know.
If this helps, mark it as a solution.
Kudos are good too.
Is there any reason why you should keep dateadded and Date columns in Date/Time datatype instead of Date(mm/dd/yyyy format)?
Try it and let us know.
If this helps, mark it as a solution.
Kudos are good too.
@VasTg, it worked like a charm, I'll leave the Date/Time variation out of the mix for the time being.
I appreciate your help!
@VasTg I'll shift to from Date/Time to Date and see if this resolves the issue.
The only reason I was keeping it in Date/Time was if I wanted to do some calculations on submission times.
I'll let you know how it goes!