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.
