cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
magnifybi
Resolver I
Resolver I

Can't use measure in DAX CALCULATE formula

Hey I'm trying to return a measure based on the below, I know I can replace "[Gross Sales Revenue]" with SUM([amount]) but there are filters in the "[Gross Sales Revenue]" I want taken into consideration, any ideas?
 
Do I have to add the filter from [Gross Sales Revnue] in this formula, I have posted both below;
 
New Customer Amount =
VAR CustomersList = VALUES('QBO General Ledger'[name])
RETURN CALCULATE([Gross Sales Revenue],
FILTER(CustomersList, CALCULATE(COUNTROWS('QBO General Ledger'), FILTER(ALLSELECTED(Dates),Dates[Date] < MIN(Dates[Date]))) = 0))
 
 
Gross Sales Revenue = CALCULATE('QBO General Ledger'[Amount Total1],FILTER('QBO General Ledger','QBO General Ledger'[Account Code (Frist Digit)] = "4"))
 
Thanks
1 ACCEPTED SOLUTION
Greg_Deckler
Super User IV
Super User IV

@magnifybi - Sorry, are you getting an error or ?

Sorry, having trouble following, can you post sample data as text and expected output?
Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.


---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

2 REPLIES 2
v-deddai1-msft
Community Support
Community Support

Hi @magnifybi ,

 

Would you please try to use the following measure:

 

New Customer Amount =
CALCULATE (
    SUM ( [amount] ),
    FILTER (
        'QBO General Ledger',
        'QBO General Ledger'[Account Code (Frist Digit)] = "4"
    ),
    CALCULATETABLE (
        VALUES ( 'QBO General Ledger'[name] ),
        FILTER (
            'QBO General Ledger',
            CALCULATE (
                COUNTROWS ( 'QBO General Ledger' ),
                FILTER ( ALLSELECTED ( Dates ), Dates[Date] < MIN ( Dates[Date] ) )
            ) = 0
        )
    )
)

 

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

 

Best Regards,

Dedmon Dai

Greg_Deckler
Super User IV
Super User IV

@magnifybi - Sorry, are you getting an error or ?

Sorry, having trouble following, can you post sample data as text and expected output?
Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.


---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors