Forum Discussion

gunther15's avatar
gunther15
Frequent Visitor
4 years ago
Solved

Excluding data query help

Hi I was wondering how I can exclude certain parts of my data but keep everything else.  For example I have data for pending quotes from 2020 - 2022, but I am only trying to exclude any pending quotes for the last 4 months that are quoted at 25,000 and above.  How would I go about doing this in either PowerBI or Dax studio and keep the rest of the pending quotes for 2020- 2022 up along with all quotes that were won, ordered, etc.  What would I need to write for the query.

Need Help
Show and Tell
  • v-kkf-msft's avatar
    v-kkf-msft
    4 years ago

    Hi gunther15 ,

     

    To include the current month please try the following measure.

    Note that the Calendar table must have the full date of the current month.

     

    Measure = 
CALCULATE (
    COUNTROWS ( 'Table' ),
    DATESINPERIOD ( 'Calendar'[Date], EOMONTH ( TODAY (), 0 ), -4, MONTH ),
    FILTER ( 'Table', 'Table'[State] = "pending" && 'Table'[Quotes] >= 25000 )
)

     

     

    If there are duplicate data on the same day, as "7/1/2022" shown in the figure, and you need to compare the summarized data (i.e. 310000), then please try the following measure.

     

    Measure2 =
CALCULATE (
    COUNTROWS ( 'Table' ),
    DATESINPERIOD ( 'Calendar'[Date], EOMONTH ( TODAY (), 0 ), -4, MONTH ),
    FILTER (
        SUMMARIZE (
            FILTER ( 'Table', 'Table'[State] = "pending" ),
            'Calendar'[Date],
            "_Quotes", CALCULATE ( SUM ( 'Table'[Quotes] ) )
        ),
        [_Quotes] >= 25000
    )
)

     

    If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
    Best Regards,
    Winniz
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

5 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    4 years ago

    gunther15 So normally you would do that kind of thing in Power Query Editor or perhaps in the source such as creating a View in SQL Server. 

  • v-kkf-msft's avatar
    v-kkf-msft
    Community Support
    4 years ago

    Hi gunther15 ,

     

    Please create the measure and show items when it is less than 1.

     

    Measure = 
CALCULATE (
    COUNTROWS ( 'Table' ),
    DATESINPERIOD ( 'Calendar'[Date], EOMONTH ( TODAY (), -1 ), -4, MONTH ),
    'Table'[State] = "pending"
        && 'Table'[Quotes] >= 25000
)

     

    If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
    Best Regards,
    Winniz
    If this post helps, then please consider Accept it as the solution to help the other members find it more quick

    • gunther15's avatar
      gunther15
      Frequent Visitor
      4 years ago

      Hi,

       

      So I put the new measurement in from what you wrote and I am getting 1s for all my pending quotes that are over 25000, not just the ones that are within 4 months.  Is there something I am doing wrong with it?

    • gunther15's avatar
      gunther15
      Frequent Visitor
      4 years ago

      Hi,

       

      I actually got the measurement query now to work.  I was wondering though how do you get it to include the current month as well.  It is only filtering out the 4 months prior to the current month and I was wondering how do you get the measurement to include July for example.  So I would want the data to be filtered out would be July, June, May and April.

      • v-kkf-msft's avatar
        v-kkf-msft
        Community Support
        4 years ago

        Hi gunther15 ,

         

        To include the current month please try the following measure.

        Note that the Calendar table must have the full date of the current month.

         

        Measure = 
CALCULATE (
    COUNTROWS ( 'Table' ),
    DATESINPERIOD ( 'Calendar'[Date], EOMONTH ( TODAY (), 0 ), -4, MONTH ),
    FILTER ( 'Table', 'Table'[State] = "pending" && 'Table'[Quotes] >= 25000 )
)

         

         

        If there are duplicate data on the same day, as "7/1/2022" shown in the figure, and you need to compare the summarized data (i.e. 310000), then please try the following measure.

         

        Measure2 =
CALCULATE (
    COUNTROWS ( 'Table' ),
    DATESINPERIOD ( 'Calendar'[Date], EOMONTH ( TODAY (), 0 ), -4, MONTH ),
    FILTER (
        SUMMARIZE (
            FILTER ( 'Table', 'Table'[State] = "pending" ),
            'Calendar'[Date],
            "_Quotes", CALCULATE ( SUM ( 'Table'[Quotes] ) )
        ),
        [_Quotes] >= 25000
    )
)

         

        If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
        Best Regards,
        Winniz
        If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.