Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
Solved! Go to Solution.
Hi,
I am not sure how your expected result looks like, but I tried to create a sample pbix file like below.
Please check the below picture and the attached pbix file.
In the sample, I tried to use GENERATE DAX Function to create a new table.
GENERATE function (DAX) - DAX | Microsoft Learn
DateTable =
GENERATE (
ADDCOLUMNS (
CALENDAR ( DATE ( 2021, 1, 1 ), DATE ( 2024, 12, 31 ) ),
"Year", YEAR ( [Date] ),
"Month Number", MONTH ( [Date] )
),
SELECTCOLUMNS (
FILTER (
FileSemesters,
( FileSemesters[StartDate] <= [Date]
&& FileSemesters[EndDate] >= [Date] )
),
"TermOfYear", FileSemesters[Semesters]
)
)
DateTable =
ADDCOLUMNS(
CALENDAR(DATE(2016, 1, 1), DATE(2024, 12, 31)),
"Year", YEAR([Date]),
"Month Number", MONTH([Date]),
"Semester",
VAR currentDate = [Date]
RETURN
MAXX(
FILTER(
FileSemesters,
FileSemesters[StartDate] <= currentDate &&
FileSemesters[EndDate] >= currentDate
),
FileSemesters[Semester]
)
)
You can then use this DateTable in your model to relate dates to semesters seamlessly.
💌 If this helped, a Kudos 👍 or Solution mark would be great! 🎉
Cheers,
Kedar
Connect on LinkedIn
hi @JG_Austra ,
try like:
DateTable =
ADDCOLUMNS(
CALENDAR(DATE(2016, 1, 1), DATE(2024, 12, 31)),
"Year", YEAR([Date]),
"Month Number", MONTH([Date]),
"Semester",
VAR currentDate = [Date]
RETURN
MAXX(
FILTER(
FileSemesters,
FileSemesters[StartDate] <= currentDate &&
FileSemesters[EndDate] >= currentDate
),
FileSemesters[Semester]
)
)
You can then use this DateTable in your model to relate dates to semesters seamlessly.
💌 If this helped, a Kudos 👍 or Solution mark would be great! 🎉
Cheers,
Kedar
Connect on LinkedIn
Hi,
I am not sure how your expected result looks like, but I tried to create a sample pbix file like below.
Please check the below picture and the attached pbix file.
In the sample, I tried to use GENERATE DAX Function to create a new table.
GENERATE function (DAX) - DAX | Microsoft Learn
DateTable =
GENERATE (
ADDCOLUMNS (
CALENDAR ( DATE ( 2021, 1, 1 ), DATE ( 2024, 12, 31 ) ),
"Year", YEAR ( [Date] ),
"Month Number", MONTH ( [Date] )
),
SELECTCOLUMNS (
FILTER (
FileSemesters,
( FileSemesters[StartDate] <= [Date]
&& FileSemesters[EndDate] >= [Date] )
),
"TermOfYear", FileSemesters[Semesters]
)
)
User | Count |
---|---|
25 | |
11 | |
8 | |
6 | |
6 |
User | Count |
---|---|
27 | |
13 | |
11 | |
9 | |
6 |