Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Using 2 Date Fields as One Slicer

Hi,   I have a table of Opportunities. In there I have 2 columns - "CreatedOn" And "ClosedOn".   I have a Card containing count of new opportunities created (Ideally Controlled by "CreatedOn"), a...
Need Help
  • v-cherch-msft's avatar
    v-cherch-msft
    7 years ago

    Hi Anonymous

     

    You may create the measures as below:

    Opportunities Created =
IF (
    ISFILTERED ( 'Calendar'[YearMonth] ),
    CALCULATE (
        COUNT ( Table1[Opportunities] ),
        FILTER ( Table1, Table1[Create] IN VALUES ( 'Calendar'[YearMonth] ) )
    )
)

    Regards,

    Cherie

v-cherch-msft's avatar
v-cherch-msft
Microsoft Employee
7 years ago

Hi Anonymous

 

You may create a calendar table and make sure there's no relationship between the two tables.Use the YearMonth column as slicer.Then you may create measures as below.For further,please refer to attached file.

Opportunities Created =
IF (
    HASONEVALUE ( 'Calendar'[YearMonth] ),
    CALCULATE (
        COUNT ( Table1[Opportunities] ),
        FILTER ( Table1, Table1[Create] = SELECTEDVALUE ( 'Calendar'[YearMonth] ) )
    )
)

Regards,

Cherie

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Hi v-cherch-msft

 

Thanks for your reply.


I am all new to Power BI, but I think I made it work when only 1 month is selected. However, if I do multiple month selections, I get a "blank". Any workaround?

  • v-cherch-msft's avatar
    v-cherch-msft
    Microsoft Employee
    7 years ago

    Hi Anonymous

     

    You may create the measures as below:

    Opportunities Created =
IF (
    ISFILTERED ( 'Calendar'[YearMonth] ),
    CALCULATE (
        COUNT ( Table1[Opportunities] ),
        FILTER ( Table1, Table1[Create] IN VALUES ( 'Calendar'[YearMonth] ) )
    )
)

    Regards,

    Cherie

    • Thomasdu43's avatar
      Thomasdu43
      New Member
      7 years ago

      Hello Cherie,

       

      I used your solution and it is effective.

       

      However, if some fieds are places I want to represent on a map, how should I do so that places and dates related?

       

      Thank you and regards.