Compound Filtering

[caloriematebloc]

I have a set of data as follows. 

date	type
…
8/31/2018	Monthly
9/30/2018	Monthly
…
9/29/2018	Daily
9/30/2018	Daily
10/1/2018	Daily
10/2/2018	Daily
10/3/2018	Daily

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.

[Greg_Deckler]

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