How to retrieve current month as a decimal number

[mmontgomery]

I am trying to retrieve the current month but as a decimal number.  I have a table like this:

I am trying to calculate the avg KM these units travel in one month. Where the measure "Month" = MONTH(NOW()) and "Avg KM per Month" = DIVIDE([KM of year],'mfive F_ISSTRANS_V'[Month])

This returns a result that would be correct at the end of April but if today was April 15th I would like "Month" to return 3.5 instead of 4.  Is there a function I can use instead of Month to retrieve this?

Thanks

[Anonymous · Accepted solution]

Hi @mmontgomery ,

I create a sample to have a test.

Data table:

DimDate Table:

DimDate = ADDCOLUMNS( CALENDARAUTO() ,"Year",YEAR([Date]),"Month",MONTH([Date]))

Relationship:

Measure:

Month Percentage = 
VAR _Month_Today =
    MONTH ( TODAY () )
VAR _Day_Today =
    DAY ( TODAY () )
VAR _MAX_MonthDay =
    DAY ( EOMONTH ( TODAY (), 0 ) )
RETURN
    _Month_Today - 1
        + DIVIDE ( _Day_Today, _MAX_MonthDay )

Avg KM per Month = 
DIVIDE ( [KM of year], [Month Percentage] )

Today is 2022/4/12, so measure will return 3.40. Result is as below.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[lbendlin]

The best thing you can do is use a Calendar table. One of the columns in that table can be the representation of the day in the month as a decimal.  

Your logic will break apart on the 30th of April though.  You will get 100% for the whole day.  If you really want to do that you would have to include the timestamp too.

[current timestamp]-[beginning timestamp of month]

------------------------------------------------------------------------------

[beginning timestamp of next month]-[beginning timestamp of month]