Master Calendar: Expression to calculate total days in a year for selected date range

[satya_s]

Hello,

I have a Master Calendar (Cal) which is joined to a fact table say FactCustomer.

The Master Calendar has the fields

Cal_Year, Cal_Month, Cal_Quarter, Cal_Week,  Cal_Total_Days_Month

The dashboard has a date range filter. 

For a selected date range say (01/01/2024 to 15/03/2024), i would like to know the total number of days (366) in that Year. 

How can I achieve this using the fields in the master calendar?

I am using the following expression but it is giving the total number of days for the selected date range and not total number of days for the selected year 

(sum({<Year={$(=Max(Cal_Year))}>}Cal_Total_Days_Month)) 

For example: If I selected date range as 01/01/2024 to 15/03/2024, I am looking to see total number of days (for the year) = 366 whereas the result shows 91 (31+29+31) 

Can someone help with the expression?

[Nicolae_Alecu]

Hello,

The result must be 91 because you have only 3 months for 2024 in your dataset. 

What is the result if you would select an interval from 2023 ? 

[Dataintellinalytics]

sum({<Year={$(=Max(Cal_Year))}>}Cal_Total_Days_Month)  you have to ignore all other filter selections in your set analysis.

Ex: sum({< EventDate=, Cal_Quarter=,  etc., etc., Year={$(=Max(Cal_Year))}>}Cal_Total_Days_Month)

[marcus_sommer]

If you don't join the calendar (which isn't recommended) to the facts else keeping it as dimension table a calculation like yours could return the wanted results - if further possible selections would be ignored, like:

sum({1<Year={$(=Max(Cal_Year))}>}Cal_Total_Days_Month)

Beside this you may consider to add the total numbers per period (not only years else also months/weeks and so on) and different day-types (calendar-days, working-days) per aggregations to the calendar, like:

left join(Calendar) load Year, sum(DayFlag) as YearTotal
resident Calendar group by Year;

and with it you would have a value for each single date - called depending of the use-case as reference or maybe per avg(YearTotal).