I want to create a measure that calculates the percentage of days complete in a month.
July has 31 days.
Today is 7/19/2018.
19/31 = .612
We are 61% complete with the month.
Solved! Go to Solution.
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 () ) ) ) ) )
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 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)
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 )
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.
January 2018 Should be 100%
May 2018 should be 100%
July should be 61%
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.
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.
I have changed this measure to a column, but am still getting the wrong value.
What am I doing wrong?
% Days Test =
var totalDays =
DateDimension[Year] = YEAR( TODAY() ) && DateDimension[Month] = MONTH( DateDimension[Date] )
var days =DAY(TODAY())
RETURN DIVIDE( days, totalDays )
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] ) ) ) )
Proud to be a Datanaut!