topic Re: Filtered YTD Total Calculated Column in DAX Commands and Tips

Filtered YTD Total Calculated Column

Anonymous — Thu, 27 Jun 2024 12:59:52 GMT

I am trying to get the YTD Revenue totals by month for only entries where [Sourcewell]= "Yes". I've tried a number of formulas I found through the forum - using an IF switch, CALCULATE with SUMX and FILTER. CALCULATE with SUMX and TOTALYTD. I ran into issues with circular dependencies or got the FY total for every entry or the filters didn't work at all.

[Sourcewell Check] and [Sourcewell] are calculated columns using LOOKUPVALUE to get data from another table. Happy to share those DAX formulas, if helpful.

I am fairly new to Power BI and know how I would do this in Excel, but cannot get it to work in BI. Attached is a sample data set.

Here are a couple of the things I tried:

SUMX( FILTER( 'Daily Revenue 2024', 'Daily Revenue 2024'[Sourcewell]= "Yes" && 'Daily Revenue 2024'[Fiscal Month Check]= 1 ), TOTALYTD(SUM('Daily Revenue 2024'[Revenue]),'Daily Revenue 2024'[Date]) )

TOTALYTD( IF( 'Daily Revenue 2024'[Sourcewell]="Yes", SUM('Daily Revenue 2024'[Revenue]),0), 'Daily Revenue 2024'[Date], ALL('Daily Revenue 2024') )

Date	Fiscal Month	Job	Revenue	Entry By	Sourcewell Check	Sourcewell	Fiscal Month Check
1-Jan-24	1	XXX-XX-XXX	$2,253.22	HL	False	No	1
1-Jan-24	1	XXX-XX-XXX	$2,220.97	HL	False	No	1
2-Jan-24	1	XXX-XX-XXX	$2,253.22	HL	False	No	1
2-Jan-24	1	XXX-XX-XXX	$2,220.97	HL	False	No	1
3-Jan-24	1	XXX-XX-XXX	$2,253.22	HL	False	No	1
3-Jan-24	1	XXX-XX-XXX	$2,220.97	HL	False	No	1
4-Jan-24	1	XXX-XX-XXX	$2,253.22	HL	False	No	1
4-Jan-24	1	XXX-XX-XXX	$2,220.97	HL	False	No	1
5-Jan-24	1	XXX-XX-XXX	$2,253.22	HL	False	No	1
5-Jan-24	1	XXX-XX-XXX	$2,220.97	HL	False	No	1
6-Jan-24	1	XXX-XX-XXX	$2,253.22	HL	False	No	1
6-Jan-24	1	XXX-XX-XXX	$2,220.97	HL	False	No	1
7-Jan-24	1	XXX-XX-XXX	$2,253.22	HL	False	No	1
7-Jan-24	1	XXX-XX-XXX	$2,220.97	HL	False	No	1
8-Jan-24	1	XXX-XX-XXX	$1,410.84	HL	False	No	1
8-Jan-24	1	XXX-XX-XXX	$18,392.00	HL	False	Yes	1
8-Jan-24	1	XXX-XX-XXX	$2,253.22	HL	False	No	1
8-Jan-24	1	XXX-XX-XXX	$2,220.97	HL	False	No	1
9-Jan-24	1	XXX-XX-XXX	$23,232.24	HL	False	Yes	1
9-Jan-24	1	XXX-XX-XXX	$2,253.22	HL	False	No	1
9-Jan-24	1	XXX-XX-XXX	$2,220.97	HL	False	No	1
10-Jan-24	1	XXX-XX-XXX	$631.80	HL	False	No	1
10-Jan-24	1	XXX-XX-XXX	$6,660.80	HL	False	Yes	1
10-Jan-24	1	XXX-XX-XXX	$5,866.87	HL	False	No	1
10-Jan-24	1	XXX-XX-XXX	$61,559.50	HL	False	Yes	1
10-Jan-24	1	XXX-XX-XXX	$17,500.00	HL	False	No	1
10-Jan-24	1	XXX-XX-XXX	$2,253.22	HL	False	No	1

Re: Filtered YTD Total Calculated Column

MaxShema — Wed, 26 Jun 2024 21:44:05 GMT

Try something like this

TOTALYTD(
SUM('Daily Revenue 2024'[Revenue]),
'Daily Revenue 2024'[Date],
'Daily Revenue 2024'[Sourcewell]= "Yes"
)

Re: Filtered YTD Total Calculated Column

Anonymous — Thu, 27 Jun 2024 12:21:03 GMT

Unfortunately that did not work, just restates the [Revenue] column

Date	Fiscal Month	Job	Revenue	Entry By	Sourcewell Check	Sourcewell	Fiscal Month Check	Column
2-May-24	5	XXX-XX-XXX	($863.55)	HL	False	Yes	5	($863.55)
1-Jun-24	6	XXX-XX-XXX	$6,086.63	HL	False	Yes	6	$6,086.63
2-Jun-24	6	XXX-XX-XXX	$20,819.00	HL	False	Yes	6	$20,819.
3-Jun-24	6	XXX-XX-XXX	$26,336.63	HL	False	Yes	6	$26,336.63
3-Jun-24	6	XXX-XX-XXX	($25,000.00)	HL	False	Yes	6	($25,000.)
7-Apr-24	4	XXX-XX-XXX	$30,720.00	HL	False	Yes	4	$30,720.
8-Apr-24	4	XXX-XX-XXX	$29,700.00	HL	False	Yes	4	$29,700.
9-Apr-24	4	XXX-XX-XXX	$34,452.00	HL	False	Yes	4	$34,452.
10-Apr-24	4	XXX-XX-XXX	$24,948.00	HL	False	Yes	4	$24,948.
11-Apr-24	4	XXX-XX-XXX	$57,240.00	HL	False	Yes	4	$57,240.
13-Apr-24	4	XXX-XX-XXX	$35,365.68	HL	False	Yes	4	$35,365.68
14-Apr-24	4	XXX-XX-XXX	$60,750.00	HL	False	Yes	4	$60,750.
24-Mar-24	3	XXX-XX-XXX	$120,000.00	HL	False	Yes	3	$120,000.
17-May-24	5	XXX-XX-XXX	$85,678.25	HL	False	Yes	5	$85,678.25
18-May-24	5	XXX-XX-XXX	$4,048.00	HL	False	Yes	5	$4,048.
19-May-24	5	XXX-XX-XXX	$1,188.00	HL	False	Yes	5	$1,188.
20-May-24	5	XXX-XX-XXX	$3,080.00	HL	False	Yes	5	$3,080.
22-May-24	5	XXX-XX-XXX	$34,759.50	HL	False	Yes	5	$34,759.5
23-May-24	5	XXX-XX-XXX	$52,362.50	HL	False	Yes	5	$52,362.5