Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply

Sum the Total days per month when filtered

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

9 REPLIES 9

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

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


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

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.

Does the date table hold duplicate values to the year? 

 

 

 

 


Connect on LinkedIn

@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


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

@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? 

@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


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

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] )
))

 

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

n1.PNG

 

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

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors