Forum Discussion
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"), 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!
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
5 Replies
Initially I thought no it's not possible but apparently it is.
Only 1 active relationship but you can derive measures using the inactive relationships. The slicer won't work on multiple connections unfortunately.
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
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?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
