Forum Discussion
Dax solution required (how to calculate frequency)
- 4 years ago
Hi saad_mce
Please refer to the sample file with the solution https://we.tl/t-txANOKHgtjActually this solution still need some work to consider working days only but I still don't fully understand your business logic. Like for example if dates are no consecutive but the gap is actually a weekend or holiday shall we count it or not. Please provide a brief description along with holidays table. I will be Little bet more complex but we can produce accurate results.
Count Result = SUMX ( SUMMARIZE ( 'Raw Data', 'Raw Data'[Date] ), VAR CurrentDate = 'Raw Data'[Date] VAR PreviousDates = FILTER ( 'Raw Data', 'Raw Data'[Date] < CurrentDate ) VAR PreviousDate = MAXX ( PreviousDates, 'Raw Data'[Date] ) RETURN IF ( CurrentDate - PreviousDate > 1, 1 ) ) - 4 years ago
Hi saad_mce
Here is the updated solution considering both weekends and holidays https://we.tl/t-2MGgJsKTAQI have included a sample holiday table. You just need to update as per your local holidays
Count Result 2 = VAR MaxDate = MAX ( 'Raw Data'[Date] ) VAR MinDate = MIN ( 'Raw Data'[Date] ) VAR T1 = CALENDAR ( MinDate, MaxDate ) VAR T2 = VALUES ( Holidays[Holidays] ) VAR T3 = FILTER ( T1, [Date] IN T2 || WEEKDAY ( [Date], 2 ) IN { 6, 7 } ) VAR T4 = SUMMARIZE ( 'Raw Data', 'Raw Data'[Date] ) VAR T5 = DISTINCT ( UNION ( T3, T4 ) ) RETURN SUMX ( T5, VAR CurrentDate = [Date] VAR PreviousDates = FILTER ( T5, [Date] < CurrentDate ) VAR PreviousDate = MAXX ( PreviousDates, [Date] ) RETURN IF ( CurrentDate - PreviousDate > 1, 1 ) )
Hi saad_mce
Please refer to the sample file with the solution https://we.tl/t-txANOKHgtj
Actually this solution still need some work to consider working days only but I still don't fully understand your business logic. Like for example if dates are no consecutive but the gap is actually a weekend or holiday shall we count it or not. Please provide a brief description along with holidays table. I will be Little bet more complex but we can produce accurate results.
Count Result =
SUMX (
SUMMARIZE ( 'Raw Data', 'Raw Data'[Date] ),
VAR CurrentDate = 'Raw Data'[Date]
VAR PreviousDates = FILTER ( 'Raw Data', 'Raw Data'[Date] < CurrentDate )
VAR PreviousDate = MAXX ( PreviousDates, 'Raw Data'[Date] )
RETURN
IF (
CurrentDate - PreviousDate > 1,
1
)
)
Hi tamerj1,
This looks good.
Business Logic: The factory is closed on Saturday and Sunday. If the production of a product continues after a gap of dates due to weekend or holidays, then the frequency is still 1 for that production. The calculation should not count the production after the weekend as a new production frequency.
I have not created a daily calendar in the data model specifying working days, weekends and holidays. My calendar is a monthly calendar showing the number working days and working hours in a month. The file reads the calendar from an excel spreadsheet. The excel spreadsheet deducts the holidays from a holidays table in the spreadsheet. Should I build a daily calendar specifying weekdays and weekends?
Regards,
Abrar
- tamerj14 years ago
Community Champion
saad_mce
The weekends are fixed. No need to be provided in a seperate table. You just need to provide a list holiday dates, one column table will be enough. It will have only few rows mostly repeated for every year.
However, now I'm facing some trouble in one case. For example of of the Product 8 shifts starts and ends in the same weekend. That makes it difficult to count. We need to igone holidays if there is no record but we need to consider it if there is a record. This is a bet confusing.