Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

% 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!

 

 

Need Help
  • v-huizhn-msft's avatar
    v-huizhn-msft
    8 years ago

    Hi Anonymous,

    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

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

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

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      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?

      • parry2k's avatar
        parry2k
        Super User
        8 years ago

        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 )
  • Vvelarde's avatar
    Vvelarde
    Community Champion
    8 years ago

    Anonymous

     

    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

  • v-huizhn-msft's avatar
    v-huizhn-msft
    Microsoft Employee
    8 years ago

    Hi Anonymous,

    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