Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hello Everyone, The SAMEPERIODLASTYEAR() doesn’t seem to work when used in a fact table that has connection with a Calendar table using an integer field (YearMonth):
But when I change the relation to a Date field suddenly it works.
The question is if I could use the time intelligence functions with a integer connection? I’m actually using a data mart that is structured this way. Thanks very much
Solved! Go to Solution.
@Anonymous - Not time intelligence functions proper, no I do not believe so, they are all wired to use dates. Which is why I posted all of the alternative approaches. Time intelligence functions are pretty useless if you stray even slightly outside the lines for how they were intended to work, which is to say:
star schema with a date table and fact and date table linked by date
If you have exactly that, time "intelligence" functions *may* actually work sometimes in certain situations as long as you really know what you are doing and understand all of the possible nuances of things like DATEADD, PARALLELPERIOD, etc.
Thanks for the content, Is there a solution for using time intelligence with a integer relation (YYYYMM) between fact and calendar table?
The calendar table is in year/month granularity.
@Anonymous For these situations I would create a date table as usual (by day) then change the fact year/month key to add in simply 01 at the end. Then use that to create the relationship between then and use that to mark the table as a date table (as @amitchandak said earlier).
Date =
var _startm = minx(Fact, Fact[YYYYMM))
var _endm = maxx(Fact, Fact[YYYYMM))
var _startdate = date(left(_startm,4),mid(_startm,5,2),1)
var _enddate = date(left(_endm,4),mid(_endm,5,2),1)
return
ADDCOLUMNS (
CALENDAR ( _startdate, _enddate ),
"Year", YEAR ( [Date] ),
"DateID",value(FORMAT([Date],"yyyyMMdd")),
"Quarter", CONCATENATE("Q",format(QUARTER ( [Date] ),"0")),
"Month", FORMAT ( [Date], "MMM" ),
"MonthNum", MONTH ( [Date] ))
In your Fact table create the DateID (here is a calculated column, but you should do this part in Transform Data or the source for larger fact tables):
DateID = value(CONCATENATE(format([MonthID],"000000"),format(01,"00")))
Even if your data doesn't go down to the daily level, this will allow you to use all the time/date intelligence DAX formulas.
Respectfully,
Zoe Douglas (DataZoe)
Follow me on LinkedIn at https://www.linkedin.com/in/zoedouglas-data
See my reports and blog at https://www.datazoepowerbi.com/
@Anonymous - Not time intelligence functions proper, no I do not believe so, they are all wired to use dates. Which is why I posted all of the alternative approaches. Time intelligence functions are pretty useless if you stray even slightly outside the lines for how they were intended to work, which is to say:
star schema with a date table and fact and date table linked by date
If you have exactly that, time "intelligence" functions *may* actually work sometimes in certain situations as long as you really know what you are doing and understand all of the possible nuances of things like DATEADD, PARALLELPERIOD, etc.
Thanks for the detail reply @Greg_Deckler , I had to insist because it will be hardworking to change the relation in the data mart, I was considering as last resort. Thanks very much!
@Anonymous Why not you calculate a first or last date of the month from YYYYMM column and then you can use it as a date to set a relationship with your date table. There is no point circling around and asking the same question when others already answered that you need date column for time intelligence, why not one extra step and create this column and make things right and work on it.
You can always find a workaround to do things but what is the point of it, I would recommend doing things in the right way so that you have a scalable solution.
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
@Anonymous You may find this helpful - https://community.powerbi.com/t5/Community-Blog/To-bleep-With-Time-Intelligence/ba-p/1260000
Also, see if my Time Intelligence the Hard Way provides a different way of accomplishing what you are going for.
https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TITHW/m-p/434008
@Anonymous , time intelligence work with date. Do you can have join on date key or date.
YYYYMMDD on both side and you can join on that or on date without time. YYYYMM will duplicate the data because date table is expected to have continuous dates
Power BI — YTD
https://medium.com/@amitchandak.1978/power-bi-ytd-questions-time-intelligence-1-5-e3174b39f38a
Power BI — QTD
https://medium.com/@amitchandak.1978/power-bi-qtd-questions-time-intelligence-2-5-d842063da839
Power BI — MTD
https://medium.com/@amitchandak.1978/power-bi-mtd-questions-time-intelligence-3-5-64b0b4a4090e
Power BI — WTD
https://medium.com/@amitchandak.1978/power-bi-wtd-questions-time-intelligence-4-5-98c30fab69d3
https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-Last-Week/ba-p/1051123
Power BI — Day Intelligence
https://medium.com/@amitchandak.1978/power-bi-day-intelligence-questions-time-intelligence-5-5-5c3243d1f9
To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/
See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-YTD-LYTD-Week-Over-Week/m-p/1051626#M184
Appreciate your Kudos.
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
64 |
User | Count |
---|---|
135 | |
120 | |
101 | |
71 | |
61 |