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

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 )
      • Anonymous's avatar
        Anonymous
        Not applicable
        8 years ago

        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:

         

        January 2018 Should be 100%

        May 2018 should be 100%

        July should be 61%