Dax Formula Number of days in a month

[joschultz]

I am trying to figure out how to calculate where we will end up for the month based on average days sales.  So if we have an average of $1500  a day and we are 5 days in the month.  I need to calulate how may days are left in the month and then mutiply that by 1500 and add that to MTD sales.  But I would like this to be dynamic so that I do not need to change the month each month.

Thank you,

Joseph

[v-jiascu-msft · Accepted solution]

Hi, Joseph

You should create a date table If you don't have a date column or a date table. It's easy. The formula is:

DateTable =
CALENDAR ( "2017-01-01", "2017-12-31" )

Then there are two measures you can use.

1. Just current month:

DaysLeftCurrentMonth =
DATEDIFF ( NOW (), EOMONTH ( NOW (), 0 ), DAY )

2. Dynamic day and month respectively.

DaysLeftDynamicMoth =
DATEDIFF ( MIN ( 'DateTable'[Date] ), ENDOFMONTH ( 'DateTable'[Date] ), DAY )

Hope this would help.

[Anonymous · Accepted solution]

Here is a formula that will do it if you don't wish to make a Date Table.

DaysinMonth = DAY( 
    IF(
        MONTH('Your Table'[Date Field]) = 12,
        DATE(YEAR('Your Table'[Date Field]) + 1,1,1),
        DATE(YEAR('Your Table'[Date Field]),  MONTH('Your Table'[Date Field]) + 1, 1)
    ) - 1
)

This just calculates the total number of days in the month.  You should be able to work it from there. 

[v-jiascu-msft · Accepted solution]

@joschultz

Hi, try this formula please. It could help.

DaysLeftCurrentQuarter =
DATEDIFF (
TODAY (),
EOMONTH (
TODAY (),
ROUNDUP ( MONTH ( TODAY () ) / 3, 0 )
* 3
- MONTH ( TODAY () )
),
DAY
)