Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

% 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
v-huizhn-msft
Employee
Employee

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

View solution in original post

10 REPLIES 10
v-huizhn-msft
Employee
Employee

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

Vvelarde
Community Champion
Community Champion

@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




Lima - Peru
Anonymous
Not applicable

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
Not applicable

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.

Anonymous
Not applicable

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:

% complete help.JPG%days2.JPG%days21.JPG

 

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.

Anonymous
Not applicable

Perfect!

 

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

 

% days help 1.JPG

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


Anonymous
Not applicable

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.