Forum Discussion

Acarrier's avatar
Acarrier
Frequent Visitor
6 years ago
Solved

Date table and DATESINPERIOD

Hi All,   I am new to power bi. I have a dataset for the production of one of our plants. Since the plant is temporarily down, we have no production records for a number of recent days. Each batch ...
  • Anonymous's avatar
    Anonymous
    6 years ago
    // 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

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Each sound and professional tabular model MUST have a user-made Calendar(s). And each such a model must be designed according to the dimensional modeling best practices.

Otherwise be prepared to suffer and, worse, create hard-to-maintain models where calculations will contain bugs you won't even be able to find.

Best
D
  • Acarrier's avatar
    Acarrier
    Frequent Visitor
    6 years ago

     

    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!

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago
      The best site in the world ever for Power BI, SSAS and all things DAX is www.sqlbi.com. The best book on DAX is "The Definitive Guide to DAX" by Marco Russo and Alberto Ferrari, the two world gurus of DAX who know EVERYTHING about it.

      Best
      D