percentage of completion of current month

[rolf1994]

Hello,

I need to create custom forecasts. To create custom forecasts I need to have a percentage of completion of the month and year so far. 

so for example, I need a measure that can do the following for me: Current date is 21st of february, I want a measure that calculates the percentage of completion of the month so far so i want it to calculate (21/28). 

I tried using the end and startofmonth function but that did not help because the functions need to be specified by a column and cannot be specified by 'Today()'

Does anyone know which measure i should use?

[v-ljerr-msft]

Hi @rolf1994,

In this scenario, I would suggest you to create an individual Calendar table(using CALENDAR DAX function) first if you don't have one yet.

Then you should be able to use the formula below to create a measure to calculate percentage of completion of current month.

percentage of completion of the month = 
CALCULATE (
    DIVIDE ( DAY ( TODAY () ), DAY ( LASTDATE ( 'Date'[Date] ) ), 0 ),
    FILTER (
        ALL ( 'Date' ),
        YEAR ( 'Date'[Date] ) = YEAR ( TODAY () )
            && MONTH ( 'Date'[Date] ) = MONTH ( TODAY () )
    )
)

Regards

[Anonymous]

I'm looking to do something similar, but I need the precentage of year complete. 

I can build a CALENDAR Table, but could somebody give me the Column Code that I would need to get the precentage. 

Thanks.

[rolf1994]

Hi @v-ljerr-msft,

I am using DirectQuery and cannot add a table. Is there another way to do this?

[v-ljerr-msft]

Hi @rolf1994,

What's your data source? Is it possible for you to add the Date table from the data source side(For example, if your data source is SQL, then you should be able to add the table within SQL).

Regards