Forum Discussion
Date table and DATESINPERIOD
// Calendar must be connected to ProdDate in the // fact table and must be marked as the Date table // in the model. [PrimeWgt LXM] = var __lastVisibleDate = MAX( Calendar[Date] ) var __numOfMonthsBack = SELECTEDVALUE( LastXMonths[LastXMonths Value] ) var __result = CALCULATE( SUM(V_FCE_STATS_BI[PrimeWgt]), DATESINPERIOD( 'Calendar'[Date], __lastVisibleDate, -__numOfMonthsBack, MONTH ) ) return IF( HASONEVALUE( LastXMonths[LastXMonths Value] ), __result )Best
D
Thank you for making clear the importance of a good date table in a data model!
I have created a date table starting from the beginning of the year of the oldest data to the end of the current year.
I have created fields like Year-Month, Year, month, day, day of week, week number etc. I will play around with it and read on best practices.
Can someone suggest good books to get familiar with power BI? I am not only interested in data models, but in DAX and visuals as well. I want to build a solid foundation of knowledge on powerBI. I have done a decent amount of database administration and data modeling in my past. So far I have listened to Guy in A cube, several documents and articles on Microsoft. There are of course many holes in my knowledge and want to be well rounded in PowerBI. So suggestions for books, sites, documents would be appreciated!
All the best!
Best
D