Calculate Percent

[SQUILES]

HI, I need help. This is the situation i have:

This is piece of my data filter by All Mondays of Dec-2020 and Jan-2021.

date	Balance1	Balance2	Total
12/7/2020	$     378.00	$  7,489.00	$    7,867.00
12/14/2020	$  4,814.00	$  7,614.00	$  12,428.00
12/21/2020	$  8,107.00	$  4,471.00	$  12,578.00
12/28/2020	$  8,731.00	$  3,148.00	$  11,879.00
1/4/2021	$  8,217.00	$     770.00	$    8,987.00

I need to Calculate the percent of All Monday of Prev Month and Each Mondays of Current Month:

Avg_Prev_Month_Monday = CALCULATE(AVERAGE('DATA '[Total]),WEEKDAY('DATA'[Date])=1,PREVIOUSMONTH('DATA'[Date]))

I got =  11,188.00 

Percent= DIVIDE([TotalBalance],[Avg_Prev_Month_Monday])-1

I got = 0.244909313 But my Table visualization bring me all dates with -100 even if a filter to show WeekDay =1.

Any suggest?

[amitchandak]

@SQUILES , few things are not clear, when need % of Monday, then you should divide sum of Monday by total, here you are dividing total by Monday

Monday = CALCULATE([TotalBalance],PREVIOUSMONTH('Date'[Date]), filter('Date',WEEKDAY('Date'[Date],2)=1))

last month = CALCULATE([TotalBalance],PREVIOUSMONTH('Date'[Date]))

last month % = divide([Monday],[last month])

Can you elaborate on calculations

[SQUILES]

Sure, 

1. I need to calculate the AVERAGE of Total Balance of all MONDAYS of Previous Month:

Avg_Prv_Month =

CALCULATE(AVERAGE(Sheet1[Total]),WEEKDAY(Sheet1[date],2)=1,PREVIOUSMONTH(Sheet1[date]))

2. The percent = Average_Monday_Prev_Month / Total_Balance of each Monday of Current Month -1

Example: Average_Monday_Prev_Month=11,188.00

1/4/2021 = (11,188   /  8,987.00) -1

1/11/2021 = (11,188 / 13692.00) -1

1/18/2021 = (11,188 / 14703.00) -1

[v-yiruan-msft · Accepted solution]

Hi @SQUILES ,

Please update the formula of measure [Avg_Prv_Month]  and [Percent] as below:

Avg_Prv_Month =
CALCULATE (
    AVERAGE ( Sheet1[Total] ),
    FILTER ( ALL ( Sheet1 ), WEEKDAY ( Sheet1[date], 2 ) = 1 ),
    PREVIOUSMONTH ( Sheet1[date] )
)

Percent= DIVIDE([Avg_Prv_Month],[TotalBalance],0)-1

Best Regards