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
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
Anonymous
Not applicable

// 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
Anonymous
Not applicable

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

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

 

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
Not applicable

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

Top Solution Authors