Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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!
Solved! Go to Solution.
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
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
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
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)
)
))
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?
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 )
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
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%
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.
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
Perfect!
I get this error when attempting to change all date dimensions:
Hi @Anonymous,
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
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 )
User | Count |
---|---|
128 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
136 | |
113 | |
102 | |
71 | |
60 |