Search code examples
powerbidatefilter

Power BI show value of previous days

I have a report filtered on a single specific day. I am trying to have a bar chart that shows the 6 previous days until the day selected, something like:

Bar chart of the 6 previous days

A sample of data:

X table data sample

The single select filter on day:

Single select filter

I want to allow the user the perform a single select on a day, and then show the values for the 6 previous day from that day, and eventually add a "year to date" column at the end. I am currently trying to reproduce a method I found here, but wonder if there is a better way to achieve it.

Solution

  • I'd suggest creating two new tables, one for your date slicer and one for your chart labels.

    The first you can do easily with a new calculated table:

    DateSlicer = VALUES(Table1[daytime])

    For the Labels table, Enter Data like this:

    Day  Index
----------
D-6  -6
D-5  -5
D-4  -4
D-3  -3
D-2  -2
D-1  -1
D     0

    With these tables set up, let's write some measures.

    ColumnDate = SELECTEDVALUE(DateSlicer[daytime]) + MAX(Labels[Index])

    This measure is to calculate which day's values to use in a particular column, which we then use in the measures to sum the values we want.

    ValueA = CALCULATE(SUM(Table1[a]), FILTER(Table1, Table1[daytime] = [ColumnDate]))
ValueB = CALCULATE(SUM(Table1[b]), FILTER(Table1, Table1[daytime] = [ColumnDate]))

    The result should look something like this:

    Output Example