Drill Through on one date but also keep previous date

[Anonymous]

Hi,

  I'm trying to design a dashboard with a drill through page. What I want is:

1. The dashboard page will show aggregated bars for each date
2. I can right click a bar and drill through on the date
3. The drill through page will have a waterfall chart to explain the difference between this date and the previous date

   For example, my data could look like this:

In the Dashboard Page, I'd like to show this Bar chart:

I'd like to drill through on one of the bars and show a waterfall chart between this date and the previous date. For example, if I drill through on 02/28/2019, I want to see this:

I can't figure out a way to keep the previous date. I think I need to do something on the data side. But how?

Thanks.

[Anonymous]

Anyone?? Thanks!

[Anonymous · Accepted solution]

@Anonymous - Please see attached pbix for one way to do it.

The following steps make up this solution:

1. In Power Query, create 3 tables:
   1. Your source table
   2. A Date Table.
   3. A table which maps the selected date with itself and with the previous date.
2. Create relationships between the 3 tables:
   1. Active relationship between date and values tables.
   2. Active Relationsip between date and Date Relationship tables. EDIT: This relationship is unnecessary.
   3. Inactive, Many-to-Many relationship between Date Relationship and Values tables.
3. Create an Altered Value measure which uses the Date Relationship dates, so that it gets current and previous values.
4. In your waterfall chart, use the Altered Value measure and the Relationship Date.

I hope this helps. If it does, please Mark as a solution.
I also appreciate Kudos.

[Anonymous]

Hi there,

  Just a follow up question. In the dashboard page, I can't use the date hierachy to enable the drillthough function. Essentially at the end I want to see my x-axis like this, and the drill through waterfall chart show similar YYYY MM format: 

Right now I have to make the x-axis in full date format (dd/mm/yyyy) to make it work. 

I tried to create a year column and a month column in the Date Relationship table, then use the new columns as drill through filters. But it's not working.

Thanks!

[Anonymous]

@Anonymous - Please see attached. Needed to adjust the Measure a bit:

Altered Value = 
var _date = LASTNONBLANK('Date'[Date],CALCULATE([Basic Value]))
return CALCULATE(
    SUM('Values'[value]),
    ALL('Date'),
    'Date Relationship'[date] = _date,
    USERELATIONSHIP('Date Relationship'[Mapping Date], 'Values'[date])
) 

[Anonymous]

Thank you!

[Anonymous]

Thank you very much! It works for my situation. And thank you for the detail Power Query Steps.