Sum the Total days per month when filtered

[chesterraepined]

I need a dax formula that would sum up all of the total days per month. I could achieve getting the number of days per month by using NumberDaysMonth = DAY(ENDOFMONTH('Calendar'[Date])) however, I need their sum when the Month slicer is filtered.

for example:

Scenario 1: When Month slicer filtered to ( January, February) the total would be 59 (31 + 28)

Scenario 2: When Month slicer filtered to ( January, February, March) the total would be 90 (31 + 28 + 31)

All help will be appreciated.

Thank you so much

[chesterraepined]

I tried using...

First, I created a new column:

NumberDaysMonth = DAY(ENDOFMONTH('TableName'[Date]))

Then I created a new measure:

Total NumberDaysMonth = SUMX(DISTINCT('TableName'[NumberDaysMonth]), [NumberDaysMonth])

however, still doesnt work since it distinct the NumberDaysMonth when filtered like (January = 31 , March = 31, May = 31 etc..)

any alternative please?

Thank you so much

[OwenAuger]

Hi @chesterraepined

Assuming your Calendar table is set up with complete months, you could use this measure:

Total NumberDaysMonth = COUNTROWS ( PARALLELPERIOD ( 'Calendar'[Date], 0, MONTH ) )

This will expand the date selection to complete months then count the number of rows (i.e. days) in Calendar.

Regards,

Owen

[chesterraepined]

Hi @OwenAuger

But my Date column doesn't contain a complete set of days. For example, in the month of January there are only 20 days. 

I need a formula that will calculate the total days based on the user filtered in slicer. I have 3 slicers: Year, Quarter and Month.

For example:

The MAX date available in my DATE column is 2018-06-18

1. If the User filtered for the YEAR (2018) and Month (January, FEb, March)

- I need an output that will count the total days from January to March, and that is 90 days

2. If the User filtered for the YEAR (2018) and Month (January, FEb, March, April, May, June)

- the total days should be 169 (31 + 28 + 31 + 30 + 31 + 18)

Hope that makes sense.

[tex628]

Does the date table hold duplicate values to the year? 

[OwenAuger]

@chesterraepined

I would strongly recommend you create a Calendar table with continguous dates and complete months, related to your fact table(s),

However if you are dealing with an incomplete date column, then use this formula instead (this assumes that at least the months selected will be contiguous):

TTotal NumberDaysMonth =
VAR MinDate =
    EOMONTH ( MIN ( 'TableName'[Date] ), -1 )
VAR MaxDate =
    EOMONTH ( MAX ( 'TableName'[Date] ), 0 )
RETURN
    1
        * ( MaxDate - MinDate )

If it is possible that non-contiguous months might be selected (such as Jan and Mar but not Feb), you could use a measure like:

Total NumberDaysMonth =
SUMX (
    SUMMARIZE (
        ADDCOLUMNS (
            VALUES ( 'TableName'[Date] ),
            "EOMonth", EOMONTH ( 'TableName'[Date], 0 )
        ),
        [EOMonth]
    ),
    DAY ( [EOMonth] )
)

Regards,

Owen

[chesterraepined]

@OwenAuger

Can you tell me how to create a Calendar table with cotinguous dates and how will I use that to get what I need? 

[OwenAuger]

@chesterraepined

Thanks for pointing out the issue with the June partial month - I had missed that when I read your earlier post.

After reading your subsequent posts, I have realised my earlier measures assumed a scenario more complicated than what you are actually doing (e.g. I assumed you might filter partial months and expect to return the full month date count).

Create calendar table

There are a number of ways to create a Calendar table, and thankfully a lot of people have written posts on this topic.

e.g.

https://exceleratorbi.com.au/build-reusable-calendar-table-power-query/

https://www.mssqltips.com/sqlservertip/4857/creating-a-date-dimension-table-in-power-bi/

https://blog.enterprisedna.co/2017/10/04/how-to-create-a-detailed-date-table-in-power-bi-fast/

https://www.sqlbi.com/articles/reference-date-table-in-dax-and-power-bi/

& many others...

To ensure time intelligence functions work correctly, it's generally best that the Calendar table contains complete months (and possibly years), though this isn't strictly required.

The essence of it is that you create a Calendar table with contiguous dates, and add whichever columns you want (month, year etc) for filtering purposes.

Create relationship with fact table(s)

Once you have a Calendar table, you should create a relationship between its Date column and the relevant Date column of any fact table.

Apply filters on columns of Calendar table

From then on, all Date-related filters should be applied on columns of the Calendar table. Also you would use the Date column of the Calendar table within any time intelligence functions.

Counting dates

In your case, once you have a relationship between the Calendar table and your fact table(s), the measure for returning the number of days for the months filtered is simply

= COUNTROWS ( 'Calendar' )

( This is simpler than the measure I originally proposed because I initially thought you might want to filter partial months but return the date count for the full months.)

But if you want dates to be counted only up to the last date present in your data, you need something like:

=
VAR MaxDateInData =
    CALCULATE ( MAX ( YourTable[Date] ), ALL ( YourTable ) )
RETURN
    CALCULATE (
        COUNTROWS ( 'Calendar' ),
        KEEPFILTERS ( 'Calendar'[Date] <= MaxDateInData )
    )

Best regards,

Owen

[chesterraepined]

Hi @OwenAuger

Thanks for always helping, I tried creating Calendar table before using =CALENDAR (DATE (2017, 01, 01), DATE (2025, 12, 31))  with additional appropriate columns needed. But when I created relationship between those two tables, it only gets the matching dates (from my primary table) to the CALENDAR table that I created in which resulting to something like "inner join". Can't find a source that will perform a "left join" (CALENDAR <------> My primary Table) relationship and still hasn't any clue. 

For now I'm using the measure that you provided and just added some tweaks on it but I really can't rely on it since it is hardcoded and I'm having a little bit of difference in the value decimal places conversion on my Average when filters applied.

Here it is:

CU Total Num of Days = IF(MONTH(MAX('CustomerUpdates-Assoc'[TransDate])) = 6,
SUMX (
 SUMMARIZE (
 ADDCOLUMNS (
 VALUES ( 'CustomerUpdates-Assoc'[TransDate] ),
 "EOMonth", EOMONTH ( 'CustomerUpdates-Assoc'[TransDate], 0 )
 ),
 [EOMonth]
 ),
 DAY ( [EOMonth] )
) - CALCULATE([CU DaysDifference]),
SUMX (
 SUMMARIZE (
 ADDCOLUMNS (
 VALUES ( 'CustomerUpdates-Assoc'[TransDate] ),
 "EOMonth", EOMONTH ( 'CustomerUpdates-Assoc'[TransDate], 0 )
 ),
 [EOMonth]
 ),
 DAY ( [EOMonth] )
))

[chesterraepined]

@OwenAuger

I don't understand the difference between the two measures you provided, I tried both measures and returns the same exact values if I filtered the slicers ex. January to June 2018 it returns 181 days. However, since the MAX or I would say the last available date of my DATE column is June 18, 2018. So the total days should only be 169. 

I created an additional measure to get the difference of the last available date (which is June 18 2018) to its current Month

A = CALCULATE(DAY(EOMONTH(MAX('TableName'[Date]),0))) - CALCULATE(DAY(MAX('TableName'[Date])))

and then I just subtract it to your measure.

TTotal NumberDaysMonth =
VAR MinDate =
    EOMONTH ( MIN ( 'TableName'[Date] ), -1 )
VAR MaxDate =
    EOMONTH ( MAX ( 'TableName'[Date] ), 0 )
RETURN
    1
        * ( MaxDate - MinDate ) - [A]

that way I'm now getting the correct total days.

But when I tried putting the measure as values to a table visual I'm not getting a FIXED value as shown below in TTotal column.

Is there a way for it to be a fixed value?