cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Acarrier
Frequent Visitor

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 has a ProdDate field for the day it was produced, PrimeWgt for the quantity produced. This is my main fact table. 

 

I have tried to make visuals using last X months where X is a parameter that can be changed. 

PrimeWgt is the measure I want to use in this example. 

 

The following does not work with TODAY(). After troubleshooting, it works if I replace TODAY() by MAX(prodDate) or any date prior to the max date of my data. I want to show last X months of data regardless of slicers (for example product type, customer etc). So if my slicers make the data sparse and many days have no production, I still want data for the last X months starting back from today. 

 

RETURN CALCULATE(sum(V_FCE_STATS_BI[PrimeWgt]), DATESINPERIOD(V_FCE_STATS_BI[ProdDate], TODAY(), -LastXMonths[LastXMonths Value], MONTH))

 
Would this be a prime candidate for adding a date table (CALENDAR function) from let's say today and back to my ealiest data's?
I guess this would also allow me to show production as zero for days where we do not operate? If I put this calendar table's end in the future ( end of the current year) is this a typical way to simplify calculations such as projected monthly production for the currenty month?
 
Is this the typical way (best practice) to architect this data?
Comments and discussion on this would be appreciated.
 
Best wishes to all!
 
1 ACCEPTED SOLUTION
daxer
Solution Sage
Solution Sage

// 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

View solution in original post

4 REPLIES 4
daxer
Solution Sage
Solution Sage

// 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

View solution in original post

daxer
Solution Sage
Solution Sage

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
Frequent Visitor

 

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!

 

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

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

April Update

Check it Out!

Click here to read more about the April 2021 Updates!

MBAS Carousel

Sign up for our May 4th event!

May the fourth be with you, join us online!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors