cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted

% Days Complete DAX

Hi, 

 

I want to create a measure that calculates the percentage of days complete in a month. 

 

Ex:

 

July has 31 days.

 

Today is 7/19/2018.

 

19/31 = .612

 

We are 61% complete with the month. 

 

Thanks!

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
v-huizhn-msft Super Contributor
Super Contributor

Re: % Days Complete DAX

Hi @ThePowerBIQueen,

Please use the following formula.

Measure =
VAR Select_mon =
    SELECTEDVALUE ( Calendario[month] )
VAR Actual_mon =
    MONTH ( TODAY () )
RETURN
    SWITCH (
        TRUE (),
        Actual_mon > Select_mon, 1,
        Actual_mon < Select_mon, 0,
        DIVIDE (
            DAY ( TODAY () ),
            CALCULATE (
                COUNTROWS ( 'Calendar' ),
                FILTER (
                    ALL ( 'Calendar' ),
                    'Calendar'[Year] = YEAR ( TODAY () )
                        && 'Calendar'[Month Number] = MONTH ( TODAY () )
                )
            )
        )
    )


Best Regards,
Angelia

10 REPLIES 10
Moscuba Member
Member

Re: % Days Complete DAX

I recommend a Calendar Table (code below)

 

Then make a measure for days in month (not in the code below but could be added):

 

Days = COUNT(Dates_INV[day])

 

Dates_INV[day] is the day number in my calendar table. Here is the code. 

 

Simply divide the day number by the Days Measure. This approach will allow the calculation to be applied to a past date, e.g., data from a table from last week.

 

Dates_INV = GENERATE (
CALENDAR( DATE( YEAR( TODAY() ) - 3, MONTH( TODAY() ), DAY( TODAY()) ), TODAY()),
VAR startOfWeek = 2 // Where 1 is Sunday and 7 is Saturday, thus a 3 would be Tuesday
VAR currentDay = [Date]
VAR days = DAY( currentDay )
VAR months = MONTH ( currentDay )
VAR years = YEAR ( currentDay )
VAR nowYear = YEAR( TODAY() )
VAR nowMonth = MONTH( TODAY() )
VAR dayIndex = DATEDIFF( currentDay, TODAY(), DAY) * -1
VAR todayNum = WEEKDAY( TODAY() )
VAR weekIndex = INT( ROUNDDOWN( ( dayIndex + -1 * IF( todayNum + startOfWeek <= 6, todayNum + startOfWeek, todayNum + startOfWeek - 7 )) / 7, 0 ) )
RETURN ROW (
"day", days,
"month", months,
"year", years,
"day index", dayIndex,
"week index", weekIndex,
"MonthNameShort", FORMAT ( months, "mmm" ),
"MonthNameLong", FORMAT ( months, "mmmm" ),
"month index", INT( (years - nowYear ) * 12 + months - nowMonth ),
"year index", INT( years - nowYear ),
"yearsort", INT( (nowYear-years)
)
))

 

Re: % Days Complete DAX

Hi, 

 

Thanks for your reply. 

 

I already have a date table and a fact table, I would rather not create a second date table. Is it a way I can do this from the current date table?

Super User
Super User

Re: % Days Complete DAX

something like this will work, assuming you have year and month number column in your calendar table

 

% Days = 
var totalDays = CALCULATE( COUNTROWS( 'Calendar' ), FILTER( ALL( 'Calendar' ),  'Calendar'[Year]  = YEAR( TODAY() ) && 'Calendar'[Month Number] = MONTH( TODAY() ) ) ) 
var days = DAY( TODAY() )
RETURN DIVIDE( days, totalDays )



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut! Connect with me on Linkedin






Re: % Days Complete DAX

Thanks for the reply!

 

This worked, but it only shows the percentage for the current month.

 

It doesn't change based on the month filter I put in.

 

Ex:

% complete help.JPG%days2.JPG%days21.JPG

 

January 2018 Should be 100%

May 2018 should be 100%

July should be 61% 

 

 

Super User
Super User

Re: % Days Complete DAX

Well in that case we need to change the dax, I just provided you based on the current date , just repace today() with selected date from the calendar in case a month/year is selected otherwise keep it today(), and rest of the logic will work.




Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut! Connect with me on Linkedin






Re: % Days Complete DAX

Perfect!

 

I get this error when attempting to change all date dimensions:

 

% days help 1.JPG

v-huizhn-msft Super Contributor
Super Contributor

Re: % Days Complete DAX

Hi @ThePowerBIQueen,

You create a calculated measure, right? You should understand the difference between the Measure and Calculated Column. The measure returns a value, while Calculated Column returns a column. The Calculation you are trying to achieve is more suitable for calculated column, change to a calculated column and check if it works fine. Please connect me if there's any issue.

Thanks,
Angelia


Re: % Days Complete DAX

@parry2k

@v-huizhn-msft

 

I have changed this measure to a column, but am still getting the wrong value. 

What am I doing wrong?

 

% Days Test =


var totalDays =

CALCULATE(

COUNTROWS( DateDimension

),

FILTER(

ALL( DateDimension

),

DateDimension[Year] = YEAR( TODAY() ) && DateDimension[Month] = MONTH( DateDimension[Date] )

)

)


var days =DAY(TODAY())
RETURN DIVIDE( days, totalDays )

Super User
Super User

Re: % Days Complete DAX

@ThePowerBIQueen

 

Hi with this:

Measure =
VAR MesSeleccionado =
    SELECTEDVALUE ( Calendario[NroMes] )
VAR MesFechaActual =
    MONTH ( TODAY () )
RETURN
    SWITCH (
        TRUE (),
        MesFechaActual > MesSeleccionado, 1,
        MesFechaActual < MesSeleccionado, 0,
        DIVIDE ( DAY ( TODAY () ), DAY ( LASTDATE ( Calendario[Date] ) ) )
    )

Regards

 

Victor




Lima - Peru
Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!