Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I was trying to create a date table for filtering.. And ran across this DAX (below) ... thinking it looked useful.
However when I went to create the table and use it.. Didn't seem to work..
example
Date =
ADDCOLUMNS (
CALENDAR (DATE (2000, 1, 1), DATE (2025, 12, 31);
“DateAsInteger”; FORMAT ( [Date]; “YYYYMMDD” );
“Year”; YEAR ( [Date] );
“Monthnumber”; FORMAT ( [Date]; “MM” );
“YearMonthnumber”; FORMAT ( [Date]; “YYYY/MM” );
“YearMonthShort”; FORMAT ( [Date]; “YYYY/mmm” );
“MonthNameShort”; FORMAT ( [Date]; “mmm” );
“MonthNameLong”; FORMAT ( [Date]; “mmmm” );
“DayOfWeekNumber”; WEEKDAY ( [Date] );
“DayOfWeek”; FORMAT ( [Date]; “dddd” );
“DayOfWeekShort”; FORMAT ( [Date]; “dddd” );
“Quarter”; “Q” & FORMAT ( [Date]; “Q” );
“YearQuarter”; FORMAT ( [Date]; “YYYY” ) & “/Q” & FORMAT ( [Date]; “Q” )
)
I took it from this dude site.. .. Anyone have any suggestions for me on how I can quickly create the date table ..
we can't calculate our fiscal year from this formula
Quarter = "Q" & ROUNDUP(MONTH(DateKey[Date])/3,0)
My fiscal year start from April & end with March
Date Range = "Datekey = CALENDAR(DATE(2014, 04, 01),DATE(2017, 03, 31))"
Please suggest me.
Thanks
Hi Giles,
Thanks for your Suggestion. I get with the first step the first error - Wrong Syntax.
I am using the actual PowerBI Desktop Version. Thanks for a Hint 😉
Kind Regards, Peter
@PeterBI You have a semi-colon where you should have a comma before "DATE(". In addition, to create this you need to click on "Modeling" - "New Table" and input the code in Table = CALENDAR(DATE(2012,01,01),DATE(2017,06,30))
Hello,
I have created the date table following your tips and instructions. However when I use the DateKey table in Quick Measure Calculations I receive following error:
Net Revenue YoY% =
IF(
ISFILTERED('DateKey'[Date]),
ERROR("Time intelligence quick measures can only be grouped or filtered by the Power BI-provided date hierarchy or primary date column."),
VAR __PREV_YEAR =
CALCULATE(
SUM('Project Details_USD'[Net Revenue]),
DATEADD('DateKey'[Date].[Date], -3, YEAR)
)
RETURN
DIVIDE(
SUM('Project Details_USD'[Net Revenue]) - __PREV_YEAR,
__PREV_YEAR
)
)
Where do I go wrong? I am not a DAX person, so I copy/paste formulas. I really need to get he Power BI-provided date hierarchy going as I do a lot of reporting using Time Intelligence. I was using Pivot Tables and Tableau Quick Table Calculations.
Thank you in advance,
Petek
Depending on your regional settings you will need to replace the , with ;
So try this instead: DateKey = CALENDAR(DATE(2012;01;01); DATE(2017;06;30))
if that works you should do the same with all the other steps.
Hi sdjensen,
this was the solution. Every comma has to be a semicolon in Germany. We have a decimal comma.
PowerBI changes some commas automatic and some not. You have to look very carefully, because some formulas work and some need manual work.
Thanks, Peter
Thanks Giles , this is awesome and very helpful .. 🙂
one more question ..
of all of these filters below i am getting the following error :
+/- 1 months = IF(AND(DateKey[Financial month number]>=[Current financial month]-1,DateKey[Financial month number]<=[Current financial month]),1,0)
"A single value for column 'Date' in table 'DateKey' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result."
@sbowles Are you entering the formula as a measure or a column? It needs to be inserted as a column, I think this may be the issue.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
100 | |
76 | |
74 | |
49 |
User | Count |
---|---|
145 | |
108 | |
107 | |
89 | |
61 |