Calculation help needed

[srk_powerbi]

Hi ,

i need osme help calculating a measure.

i have below 2 tables. Both are related by AccountNum coulumn.  I need to calculate Measures for

1. calculate the Count(AccountNum) where RegisteredDate=  Today

2. calculate the Count(AccountNum) where RegisteredDate=  Previous Businessday

3. Calculate the SUM(Amount) where RegisteredDate=  Today

4. Calculate the SUM(Amount) where RegisteredDate=  Previous Businessday

Please help.  I dont see option to attach pbix here.

table 1

AccountNum	Name	RegisteredDate	CompletedDate
470093594	Jeff	11/28/2020	12/1/2020
470101606	Vicky	11/30/2020	11/30/2020
470107112	Kevin	12/3/2020	NULL
470099664	Peter	12/1/2020	12/2/2020
720109049	Victor	11/25/2020	NULL
470108423	Rachel	11/24/2020	11/30/2020
470104785	Ian	12/3/2020	NULL
470109222	Nicky	12/2/2020	12/3/2020
720108958	Carl	11/29/2020	12/1/2020

table 2

AccountNum	Amount
470093594	$                               2,000.00
470101606	$                               3,500.00
470107112	$                                  800.00
470099664	$                               2,100.00
720109049	$                               1,600.00
470108423	$                               1,730.00
470104785	$                                  950.00
470109222	$                               3,800.00
720108958	$                               1,250.00

[Anonymous · Accepted solution]

Hi @srk_powerbi ，

Based on your description, you can create some measures as follows.

count_today = CALCULATE(COUNT('Table 1'[AccountNum]),'Table 1'[RegisteredDate]=TODAY()-1)

count_previous =

var x1 = MAXX(FILTER(ALL('Table 1'),[RegisteredDate]<TODAY()-1&&WEEKDAY('Table 1'[RegisteredDate],2)<6),'Table 1'[RegisteredDate])

return

CALCULATE(COUNT('Table 1'[AccountNum]),'Table 1'[RegisteredDate]=x1)

sum_today = CALCULATE(SUM('Table 2'[Amount]),'Table 1'[RegisteredDate]=TODAY()-1)

sum_previous =

var x1 = MAXX(FILTER(ALL('Table 1'),[RegisteredDate]<TODAY()-1&&WEEKDAY('Table 1'[RegisteredDate],2)<6),'Table 1'[RegisteredDate])

return

CALCULATE(SUM('Table 2'[Amount]),'Table 1'[RegisteredDate]=x1)

Result:

Hope that's what you were looking for.

Best Regards,

Yuna

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

I assume that you have a calender table, try adding following column:

PrevBusinessdAy = 
	MAXX(
		FILTER('Calendar';'Calendar'[Date] < EARLIER('Calendar'[Date])
			&& WEEKDAY('Calendar'[Date];3) <5); 
		'Calendar'[Date] 
        )

 Then create the measures.

[amitchandak]

@srk_powerbi , Create a date Table with following columns

Work Day = if(WEEKDAY([Date],2)>=6,0,1)
Work Date = if(WEEKDAY([Date],2)>=6,BLANK(),[Date])
Work Date Cont = if([Work Day]=0,maxx(FILTER('Date',[Date]<EARLIER([Date]) && [Work Day]<> EARLIER([Work Day]) ),[Date]),[Date])
Work Date cont Rank = RANKX(ALL('Date'),[Work Date Cont],,ASC,Dense)

Join it with Register Date

This Day = CALCULATE(sum('Table'[Amount]), FILTER(ALL('Date'),'Date'[Work Date cont Rank]=max('Date'[Work Date cont Rank])))
Last work day = CALCULATE(sum('Table'[Amount]), FILTER(ALL('Date'),'Date'[Work Date cont Rank]=max('Date'[Work Date cont Rank])-1))
diff =[This Day] - [Last work day]