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

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% 

 

 

parry2k's avatar
parry2k
Super User
8 years ago

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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    Perfect!

     

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

     

    • v-huizhn-msft's avatar
      v-huizhn-msft
      Microsoft Employee
      8 years ago

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

        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 )