cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Regular Visitor

Compound Filtering

I have a set of data as follows. 

 

datetype
 
8/31/2018Monthly
9/30/2018Monthly
 
9/29/2018Daily
9/30/2018Daily
10/1/2018Daily
10/2/2018Daily
10/3/2018Daily

 

Daily and Monthly record level data are saved to the same table for the following reasons:

  • I should be able to choose one date freely from the daily dates in a slicer or similiar feature.
  • Based on the date I choose, I need to see the prior 12m of monthly data. My pivot should have 13 columns: the rightmost column is the daily date I choose, the other 12 are the preceding 12 months.
  • As I move my slider thru the daily range, the preceeding 12 months should dynamically change, so when I select daily value 10/3/18 my most recent monthly date should be 9/30/2018. When I select daily value 9/29/2018, my most recent monthly date should be 8/31/18, etc.
  • Some may suggest I construct my monthly numbers based on an aggregation of a sufficient history of dailies. This will not be the correct approach because these are measures of point in time inventories that should not sum together.
  • Some dates (such as 9/30/18) will have both a daily and special month end result. The data for month end has been more carefully reviewed/cleaned up, and is considered "official" vs the "unofficial" daily result. We need to blend both results seamlessly as described above -> I should only see one daily result at a time.
  • The additional requirements are that the solution must support directquery.
  • Marketplace visuals can be used, but must be able to be published to the Power BI Service.
1 REPLY 1
Highlighted
Super User IV
Super User IV

Re: Compound Filtering

Can you mock up what the output would look like?

 

Also, what does the rest of your data look like? Apparently you have some kind of value?

 

Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490


---------------------------------------

Putting square pegs in round holes since 1972.

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements
August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Community Summit Australia – Join Online!

Community Summit Australia – Join Online!

Be a part of the leading Microsoft Business Applications digital event, curated for the APAC community.

Top Solution Authors
Top Kudoed Authors