cancel
Showing results for
Did you mean:
Highlighted
Member

## % 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
Super Contributor

## Re: % Days Complete DAX

Hi @ThePowerBIQueen,

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

Member

## Re: % Days Complete DAX

Hi,

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

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

Proud to be a Datanaut! Connect with me on Linkedin

Member

## Re: % Days Complete DAX

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%

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.

Proud to be a Datanaut! Connect with me on Linkedin

Member

## Re: % Days Complete DAX

Perfect!

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

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

Member

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

## Re: % Days Complete DAX

@ThePowerBIQueen

Hi with this:

```Measure =
SELECTEDVALUE ( Calendario[NroMes] )
VAR MesFechaActual =
MONTH ( TODAY () )
RETURN
SWITCH (
TRUE (),
DIVIDE ( DAY ( TODAY () ), DAY ( LASTDATE ( Calendario[Date] ) ) )
)```

Regards

Victor

Lima - Peru