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
)

[Sachet_716]

Hi!

How did you do yours? I think we have the same issue. It is important for me to compute projected sales for the entire month with my current MTD sales  😞  need your help.

Thank you.

cristina

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

[Anupa_Jayakody]

Hi 

Thank you for the answer, why is it returning 29 days for certain months??

[joschultz]

This works great!

how can I modify this formula for days left in QTR?

[v-jiascu-msft]

@joschultz

Hi, 

It's easy. You can replace the ENDOFMONTH with ENDOFQUARTER.

DaysLeftDynamicQuarter =
DATEDIFF ( MIN ( DateTable[Date] ), ENDOFQUARTER ( DateTable[Date] ), DAY )

But I haven't figured out the formula for TODAY. If you need it, please let me know. I will be here to help. 

[joschultz]

When I use the dynamic one it doesnt decrease.  the number stays the same. 

[joschultz]

This does give me the number of days left however it doesnt work to use in a calulation.  What I have that works is to use this formula to get the number of days in current month =  DATEDIFF(EOMONTH(NOW(),-1),(NOW()-1),day).  This way I can divide this measure into th MTD measure and get a daily total.  Is there a way to convert that formula to get number of days in current qtr?

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

[joschultz]

Thank you!!!  Could you change that formula so that it shows me number of days in the current quarter?

Thank you,

Joseph

[joschultz]

I modified it to give me this

days in = DATEDIFF(EOMONTH(TODAY(),ROUNDDOWN(MONTH(TODAY()) /3,0)*3-MONTH(TODAY())),TODAY(),DAY)

Which gives me 42 days and the portion

EOMONTH(TODAY(),(ROUNDDOWN(MONTH(TODAY()) /3,0)*3-MONTH(TODAY())))

Gives me the date of 3/31/2017.  I tried doing a +1 to get it to the first but that puts it at the end of april. I just want it to start from 4/1/2017. 

Can you help me add one day to the that?

Thank you!

[v-jiascu-msft · Accepted solution]

@joschultz

Hi, Joseph

Thank you for marking my answers. You should add +1 with your entire first parameter. Or you can try the another one. They both works well. Good luck!

days in =
DATEDIFF (
    EOMONTH (
        TODAY (),
        ROUNDDOWN ( MONTH ( TODAY () ) / 3, 0 )
            * 3
            - MONTH ( TODAY () )
    )
        + 1,
    TODAY (),
    DAY
)

days in =
DATEDIFF (
    DATE ( YEAR ( TODAY () ), ROUNDDOWN ( MONTH ( TODAY () ) / 3, 0 ) * 3 + 1, 1 ),
    TODAY (),
    DAY
)

[joschultz]

That worked!  One other question.  So I have MTD calcuation for the value actual.  but for the value planned I am trying to to MTD but only want through the current date.  In the data set there is data for planned for the entire month.

[v-jiascu-msft]

@joschultz

Hi,

Is the scenario in the picture what you want? Here are the two formulas. You can have a try. (The blue part you can make some changes.)

ActualMTD =
CALCULATE (
    TOTALMTD ( SUM ( Table1[Actual] ), Table1[Date] ),
    Table1[Actual] > 0
)

PlannedMTD =
CALCULATE (
    TOTALMTD ( SUM ( Table1[Planned] ), Table1[Date] ),
    Table1[Actual] > 0
)

Best Regards.

Dale

[v-jiascu-msft]

@joschultz

Hi, if you want to use this formula in a table as a calculated column, you will have to wrap a CALCULATE like this. You can have a try.

 dynamic =
CALCULATE (
DATEDIFF ( MIN ( DateTable[Date] ), ENDOFMONTH ( DateTable[Date] ), DAY )
)

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

[Greg_Deckler]

Create a date table and have the number of days in the month in that table. You can use DAY function to get the day of the month and a simple calculation on your related date table to get the number of days left.