Forum Discussion

azavicar's avatar
azavicar
Frequent Visitor
3 years ago
Solved

Calculation with dynamic filter assistance

Hello everyone,

 

I have been struggling with the DAX behind this calculation and could use suggestions as to how to proceed forward.

The model that I have built uses data derrived from all SQL queries.  The relevant tables include: Calendar, Clients, and Quotes.
The relationships between these tables are:
Calander to Quotes, 1 to Many (Based on 'Calendar'[Date] to 'Quotes'[Date])
Clients to Quote, 1 to Many (Based on the 'Clients'[ClientID] to 'Quotes'[ClientID])


The end user has a date range slider that allows them to select the starting period of the displayed data.

I am trying to write a calulation that will sum the total quotes during the selected period but only if the clients quote total is zero in the two previous quarters. 

Thank you.

Update:

Below is one example of a measure that was unsuccessful.

 

TotalNewQuotes =
SUMX (
    FILTER (
        'QuotesSum',
        'QuotesSum'[DATE] >= DATEADD ( 'Calendar'[Date], -6, QUARTER )
            && 'QuotesSum'[DATE] < 'Calendar'[Startdate]
    ),
    IF (
        CALCULATE (
            COUNTROWS ( 'QuotesSum' ),
            DATESINPERIOD ( 'Calendar'[Date], LASTDATE ( 'Calendar'[Date] ), -2, QUARTER )
        )
            = 0,
        'QuotesSum'[TotalQuotes],
        BLANK ()
    )
)

The start date measure referenced in the above statement is:
Startdate = CALCULATE(MIN('Calendar'[Date]),ALLSELECTED('Calendar'[Date]))

This overall measure results in (Blank). No filters are allied to the visual.


Need Help
  • azavicar's avatar
    azavicar
    3 years ago

    Found a way around this by creating a summary table and then measures with a swtich statement.  Thank you all that viewed this.

3 Replies

  • v-zhangti's avatar
    v-zhangti
    Community Support
    3 years ago

    Hi, azavicar 

     

    Can you provide sample data for testing? Sensitive information can be removed in advance. What kind of expected results do you expect? You can also show it with pictures or Excel. I look forward to your response.

     

    Best Regards,

    Community Support Team _Charlotte

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

  • azavicar's avatar
    azavicar
    Frequent Visitor
    3 years ago

    Found a way around this by creating a summary table and then measures with a swtich statement.  Thank you all that viewed this.