Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Using Time Intelligence functions with a integer relationship to Calendar

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

GustavoSantana__0-1598361343224.png

GustavoSantana__0-1598361523422.png

 

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

1 ACCEPTED 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.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

7 REPLIES 7
Anonymous
Not applicable

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.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

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.

Greg_Deckler
Super User
Super User

@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


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
amitchandak
Super User
Super User

@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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.