How to find/count number of days for selected months and year without AGGR

[kiru_18]

Hi,

I am using Sum(Aggr(Day(MonthEnd(Max(DateSet))), Month, Year)) to get total number of days for selected month and year. Can we use set analysis to achieve this?

Example : If I select Feb 2020, Mar 2020, Feb 2021, Mar 2021 , Result  - no of days = 119

     If I select Jan 2020, May 2020. Result - no of days = 62.

Can anyone please help me to get this without using AGGR in expression.

[Gysbert_Wassenaar]

perhaps like this: Max( DateSet) - Min(DateSet) + 1

[kiru_18]

It's not working. Giving wrong answer. If i select Jan 2021 and Mar 2021 it's showing 90 days. But result should be 31(for Jan)+31(for Mar) = 62 days

[kiru_18]

Can anyone give solution with if statement or set expression. Ques: Count number if days for selected month(multiple selection in list box)

EX: If Mar 2021 and 2020 are selected. Output should be 31+31 = 62 days

  If Jan 2020, Feb 2020 and June 2020 are selected, then Output should be 31+29+30 = 90 days

[marcus_sommer]

I wouldn't try it in this way else using appropriate information within a master-calendar, like defining a 1 for each calendar/working-day and then just applying sum(DayValue) or you may also calculate the max/kum days there and using max/avg() to pick them.

- Marcus

[kiru_18]

If i didn't have record on particular date then result is wrong. If May month has only records for 20 date. Then if I select May month it's show sum(dayvalue) is 20. But result I need to get is total days in month (i.e 31 days in May month) 

[MayilVahanan]

HI @kiru_18 

Hope you've Date field in your data model which is derived from Master Calendar. 

In that case, You can use

count(Distinct Date)

Suppose, you don't have records in fact table & dimension table info, you can remove the those selections with help of set analysis.

Count({<YourDim1=, YourDim2=>}Distinct Date)

Else, you need to create dummy value for missed dates in your fact table to resolve this.