Using 2 Date Fields as One Slicer

[PeterStuhr]

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"), and another card showing Revenue of won Opportunities (Ideally Controlled by "ClosedOn".

It is possible to make a "combined" slicer, so If I select "Jan 2019", the "Opportunities Created" will be sorted by "CreatedOn" Jan 2019, AND the "Revenue of Won Opportunities" will be sorted by "ClosedOn" Jan 2019?

Hope it makes sense!

[v-cherch-msft]

Hi @PeterStuhr

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

[PeterStuhr]

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 · Accepted solution]

Hi @PeterStuhr

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]

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. 

[gooranga1]

Initially I thought no it's not possible but apparently it is. 

https://community.powerbi.com/t5/Desktop/Date-dimension-and-multiple-date-columns-in-fact-table/td-p...

Only 1 active relationship but you can derive measures using the inactive relationships. The slicer won't work on multiple connections unfortunately.