Matrix showing sum of transactions for last 7 days by day

DAX DAX Calculations
1

I am trying to do the following.

I want to create a matrix. In the rows, I want the dates for the last 7 days. In the values section I want the sum of transactions.

I’m looking for something like this…
image
(** notice since there are no transactions on the weekend, June 27 and June 28 are not on a row**)

When I first tried this, I put a filter on the visual using the Day Offset in my calendar table. Basically Calendar[Day Offset]>=-7.

The problem is that a blank date shows up on the top of the matrix, with a sum of all the transactions beyond the 7 days. It looks like this…
image
(**Also the total is for all transaction dates not just the last 7 days)

Thanks for the help

2

@BillK,

Give this a go:

Transactions Last 7 Days = 
CALCULATE(
    COUNTROWS( Sales ),
    FILTER(
        Dates,
        Dates[Date] <= TODAY() &&
        Dates[Date] >= TODAY() - 6
    )
)

image

I hope this is helpful. Full solution file below.

3

In addition, you can also set up a Period table in Power Query, Jarrett has posted all details here.

.
I hope this is helpful.

4

BrianJ,

Thanks. Exactly what I needed.

Bill

5

@BillK,

:+1:. Great- glad to hear that worked well for you.

  • Brian
6

Melissa,
Thank you for posting this. It is really exciting. This opens up a huge range of possibilities. It will take me some time to figure it out, but knowing it is possible and having the roadmap is a huge help.
Regards,
Bill

1 Like