Urgent Help to Calculate Percentage of Date Remaining in a Month dynamically

[Anonymous]

Hello,

I am trying to add a rotating tile which displays Number of days in current month remaining and i got it to work using

Days Left in Month = INT(EOMONTH(TODAY(), 0) - TODAY())

And on the other side i need to display % of days remaining in current month. I tried to do this using 

Workdays Passed in Month AS Percent =

    VAR WorkdaysLeftinMonth = COUNTROWS(

    FILTER(

        CALENDAR(

            TODAY(),

            EOMONTH(TODAY(), 0)

        ),

        WEEKDAY([Date], 1) < 7

    )

)

VAR TotalWorkDaysInMonth = COUNTROWS(

            FILTER(

                CALENDAR(

                    DATE(MONTH(TODAY()),1,1),

                    DATE(MONTH(TODAY())+1,1,1)-1

                ),

                WEEKDAY([Date], 1) < 7

            )

        )   

RETURN DIVIDE(WorkdaysLeftinMonth,TotalWorkDaysInMonth,0)

It shows 7% remaining which is wrong. Need to fix this ASAP. My calendar table is

Dates_Dim =

GENERATE (

CALENDAR ( DATE ( Year(TODAY()), 0, 1 ), DATE ( Year(TODAY()),MONTH(TODAY()),31)),

VAR currentDay = [Date]

VAR day = DAY( currentDay )

VAR month = MONTH ( currentDay )

VAR year = YEAR ( currentDay ) var Month_name = FORMAT([Date], "MMM")

RETURN ROW (

"day", day,

"month", month, "Month_name",Month_name,

"year", year )

)

By this i limit to current Month. Now if i select September i need the rotating tile to display 0 as number of days left because september is over and 100% as % of days left.

Thanks

[v-juanli-msft]

Hi @Anonymous 

Is this problem sloved? 

If it is sloved, could you kindly accept it as a solution to close this case?

If not, please let me know.

Best Regards

Maggie

[v-juanli-msft]

Hi @Anonymous 

If Watsky's reply solve your problem, please accept it as a solution so others may find it quickly.

If not, please refer to my solution.

Create measures

TODAY = TODAY()

TOTAL DAYS THIS MONTH = CALCULATE(COUNT(Dates_Dim[day]),FILTER(ALL(Dates_Dim),Dates_Dim[year]=YEAR([TODAY])&&Dates_Dim[month]=MONTH([TODAY])))

REMAING DAYS = CALCULATE(COUNT(Dates_Dim[day]),FILTER(ALL(Dates_Dim),Dates_Dim[year]=YEAR([TODAY])&&Dates_Dim[month]=MONTH([TODAY])&&Dates_Dim[Date]>[TODAY]))

% = [REMAING DAYS]/[TOTAL DAYS THIS MONTH]

If we change [Today] measure as below

TODAY = DATE(2019,10,7)

Best Regards
Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Watsky]

See if this works:

First add a new column to your Date table:

WorkDay = IF(WEEKDAY(Dates_Dim[Date],2)<6,1,0)

Then try this measure:

Workdays Passed in Month AS Percent = 
   
VAR ThisToday = TODAY()
VAR ThisMonthStart = DATE(year(ThisToday),MONTH(ThisToday),1)
VAR EOM = EOMONTH(ThisMonthStart,0)

VAR BIZWEEK = MOD(
    5 + WEEKDAY(
        TODAY() + 1) - 
            WEEKDAY(EOMONTH(TODAY(),0)-1),
            5)

VAR TotalWorkDaysInMonth =  CALCULATE(
    SUM(Dates_Dim[Workdday])
    ,ALL(Dates_Dim)
, DATESBETWEEN(Dates_Dim[Date],ThisMonthStart,EOM)

)
RETURN
    DIVIDE(TotalWorkDaysInMonth - BIZWEEK,TotalWorkDaysInMonth)

This returned 83% for me. Let me know if that worked for you.

[Anonymous]

@Watsky 

I tweaked yours 

First add a new column to your Date table:

WorkDay = IF(WEEKDAY(Dates_Dim[Date],1)<8,1,0)

Then try this measure:

Workdays Passed in Month AS Percent = 
   
VAR ThisToday = TODAY()
VAR ThisMonthStart = DATE(year(ThisToday),MONTH(ThisToday),1)
VAR EOM = EOMONTH(ThisMonthStart,0)

VAR BIZWEEK = MOD(
    7 + WEEKDAY(
        TODAY() + 1) - 
            WEEKDAY(EOMONTH(TODAY(),0)-1),
            7)

VAR TotalWorkDaysInMonth =  CALCULATE(
    SUM(Dates_Dim[Workdday])
    ,ALL(Dates_Dim)
, DATESBETWEEN(Dates_Dim[Date],ThisMonthStart,EOM)

)
RETURN
    DIVIDE(TotalWorkDaysInMonth - BIZWEEK,TotalWorkDaysInMonth)

It worked. But when i selected September it still shows values for current month. For september it should say days remaining as 0 and % as 100%

[Anonymous]

Any help on this?

[Watsky]

@Anonymous  I changed all the Today() to the date range instead. Give this a shot.

Workdays Passed in Month AS Percent = 
   
VAR ThisToday = TODAY()
VAR ThisMonthStart = STARTOFMONTH(Dates_Dim[Date])
VAR ThisMonthEnd = ENDOFMONTH(Dates_Dim[Date])

VAR BIZWEEK = MOD(
    5 + WEEKDAY(
        ThisMonthStart+ 1,1) - 
            WEEKDAY(ThisMonthEnd,1)-1,
            5)

VAR TotalWorkDaysInMonth =  CALCULATE(
    SUM(Dates_Dim[Workdday])
    ,ALL(Dates_Dim)
, DATESBETWEEN(Dates_Dim[Date],ThisMonthStart,ThisMonthEnd)

)
RETURN
   IF(MONTH(ThisMonthEnd)=MONTH(ThisToday), DIVIDE(TotalWorkDaysInMonth-BIZWEEK,TotalWorkDaysInMonth),1)

[Anonymous]

@Watsky  I used below and it is returning 84%. Value should be 77.4%

% Workdays Passed in Month =

VAR ThisToday = TODAY()

VAR ThisMonthStart = STARTOFMONTH('Dates_Dim'[Date])

VAR ThisMonthEnd = ENDOFMONTH('Dates_Dim'[Date])

VAR BIZWEEK = MOD(

7 + WEEKDAY(

ThisMonthStart+ 1,1) -

WEEKDAY(ThisMonthEnd,1)-1,

7)

VAR TotalWorkDaysInMonth = CALCULATE(

SUM('Dates_Dim'[WorkDay])

,ALL('Dates_Dim')

, DATESBETWEEN('Dates_Dim'[Date],ThisMonthStart,ThisMonthEnd)

)

RETURN

IF(MONTH(ThisMonthEnd)=MONTH(ThisToday), DIVIDE(TotalWorkDaysInMonth-BIZWEEK,TotalWorkDaysInMonth),1)

Please tell where I am going wrong.

[Watsky]

Not sure how you get 77.4% the best I could come up with is 77.27%

% Workdays Passed in Month = 
 
VAR ThisToday = TODAY()
VAR ThisMonthStart = STARTOFMONTH('Dates_Dim'[Date])
VAR ThisMonthEnd = ENDOFMONTH('Dates_Dim'[Date])

VAR BIZWEEK = MOD(
7 + WEEKDAY(
ThisMonthStart,1) -
WEEKDAY(ThisMonthEnd,1),
7)

VAR TotalWorkDaysInMonth = CALCULATE(
SUM(Dates_Dim[Workdday])
,ALL('Dates_Dim')
, DATESBETWEEN('Dates_Dim'[Date],(ThisMonthStart),(ThisMonthEnd)-1)

)
RETURN
IF(MONTH(ThisMonthEnd)=MONTH(ThisToday), DIVIDE((TotalWorkDaysInMonth-BIZWEEK),TotalWorkDaysInMonth),1)

[Anonymous]

@Watsky  Thanks for response. But even this returned 83%

[Watsky]

I'm not sure why but yours is returning different than mine. 

[Watsky]

@Anonymous  I'm still looking in to it.

[Anonymous]

@Watsky 

Thanks! I am looking for calendar days and not working days.