Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Filtered YTD Total Calculated Column

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')
	)

 

 

 

 

DateFiscal MonthJobRevenueEntry BySourcewell CheckSourcewellFiscal Month Check
1-Jan-241XXX-XX-XXX$2,253.22HLFalseNo1
1-Jan-241XXX-XX-XXX$2,220.97HLFalseNo1
2-Jan-241XXX-XX-XXX$2,253.22HLFalseNo1
2-Jan-241XXX-XX-XXX$2,220.97HLFalseNo1
3-Jan-241XXX-XX-XXX$2,253.22HLFalseNo1
3-Jan-241XXX-XX-XXX$2,220.97HLFalseNo1
4-Jan-241XXX-XX-XXX$2,253.22HLFalseNo1
4-Jan-241XXX-XX-XXX$2,220.97HLFalseNo1
5-Jan-241XXX-XX-XXX$2,253.22HLFalseNo1
5-Jan-241XXX-XX-XXX$2,220.97HLFalseNo1
6-Jan-241XXX-XX-XXX$2,253.22HLFalseNo1
6-Jan-241XXX-XX-XXX$2,220.97HLFalseNo1
7-Jan-241XXX-XX-XXX$2,253.22HLFalseNo1
7-Jan-241XXX-XX-XXX$2,220.97HLFalseNo1
8-Jan-241XXX-XX-XXX$1,410.84HLFalseNo1
8-Jan-241XXX-XX-XXX$18,392.00HLFalseYes1
8-Jan-241XXX-XX-XXX$2,253.22HLFalseNo1
8-Jan-241XXX-XX-XXX$2,220.97HLFalseNo1
9-Jan-241XXX-XX-XXX$23,232.24HLFalseYes1
9-Jan-241XXX-XX-XXX$2,253.22HLFalseNo1
9-Jan-241XXX-XX-XXX$2,220.97HLFalseNo1
10-Jan-241XXX-XX-XXX$631.80HLFalseNo1
10-Jan-241XXX-XX-XXX$6,660.80HLFalseYes1
10-Jan-241XXX-XX-XXX$5,866.87HLFalseNo1
10-Jan-241XXX-XX-XXX$61,559.50HLFalseYes1
10-Jan-241XXX-XX-XXX$17,500.00HLFalseNo1
10-Jan-241XXX-XX-XXX$2,253.22HLFalseNo1
Need Help

2 Replies

  • MaxShema's avatar
    MaxShema
    Regular Visitor
    2 years ago

    Hi

     

    Try something like this

     

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

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

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

       

      DateFiscal MonthJobRevenueEntry BySourcewell CheckSourcewellFiscal Month CheckColumn
      2-May-245XXX-XX-XXX($863.55)HLFalseYes5($863.55)
      1-Jun-246XXX-XX-XXX$6,086.63HLFalseYes6$6,086.63
      2-Jun-246XXX-XX-XXX$20,819.00HLFalseYes6$20,819.
      3-Jun-246XXX-XX-XXX$26,336.63HLFalseYes6$26,336.63
      3-Jun-246XXX-XX-XXX($25,000.00)HLFalseYes6($25,000.)
      7-Apr-244XXX-XX-XXX$30,720.00HLFalseYes4$30,720.
      8-Apr-244XXX-XX-XXX$29,700.00HLFalseYes4$29,700.
      9-Apr-244XXX-XX-XXX$34,452.00HLFalseYes4$34,452.
      10-Apr-244XXX-XX-XXX$24,948.00HLFalseYes4$24,948.
      11-Apr-244XXX-XX-XXX$57,240.00HLFalseYes4$57,240.
      13-Apr-244XXX-XX-XXX$35,365.68HLFalseYes4$35,365.68
      14-Apr-244XXX-XX-XXX$60,750.00HLFalseYes4$60,750.
      24-Mar-243XXX-XX-XXX$120,000.00HLFalseYes3$120,000.
      17-May-245XXX-XX-XXX$85,678.25HLFalseYes5$85,678.25
      18-May-245XXX-XX-XXX$4,048.00HLFalseYes5$4,048.
      19-May-245XXX-XX-XXX$1,188.00HLFalseYes5$1,188.
      20-May-245XXX-XX-XXX$3,080.00HLFalseYes5$3,080.
      22-May-245XXX-XX-XXX$34,759.50HLFalseYes5$34,759.5
      23-May-245XXX-XX-XXX$52,362.50HLFalseYes5$52,362.5