Showing last 7 days and monthly total in one matrix

[MReinagl]

Hi,

I have sales figures for different products per day and want to build a report showing:

- sales for each of the last seven days per product

- total sales for the current month per product

- average sales per day for the current week

I managed to create a measure which summarised the sales for the last seven days which is then shown in the matrix:

How can I now add the monthly total and maybe the average sales per day for the current week?

Thanks

Matthias

[v-yetao1-msft · Accepted solution]

Hi @MReinagl 

Please correct me if I wrongly understood your question.

(1)We can achieve monthly total through the time hierarchy.

Original matrix visual

Use time hierarchy function:

(2)Create a column to get the average sales per day for the current week .And then add the column in Vaules .

 average = CALCULATE(AVERAGE('Table'[Price]),FILTER('Table','Table'[Date]>MAX('Table'[Date])-7 && 'Table'[Date]<= MAX('Table'[Date])))

I have attached my pbix file, you can refer to it .

Best Regards

Community Support Team _ Ailsa Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[MReinagl]

Hi,

this does not really help me I'm afraid.

I want to show the sales for the seven days and then next to it the average across all seven days. Basically like this:

                  Mon   Tue   Wed   Thu   Fri   Sat   Sun    AVG
Product A 100      110      90     90  110  100   100     100
Product B   80        80      70     70    60    50     50   65,71
Product C   40        60      60     60    40    40     40   48,57

Thanks

Matthias

[MReinagl]

OK, I managed to create a measure to show the month-to-date amount. But how do I manage now that this MTD amount is shown only once, i.e. for the last day:

The measure I created is: 

Nettosumme_MTD = CALCULATE(

    TOTALMTD(SUM(tblDATA[Nettosumme]),

    Datum[Date])

)

[selimovd]

Hey @MReinagl ,

you can calculate the average for the last 7 days like that:

Average last 7 days = 
CALCULATE (
    AVERAGE(myTable[myColumn]),
    DATESINPERIOD (
        'Date'[Date],
        MAX ( 'Date'[Date] ),
        -7,
        DAY
    )
)

If you need any help please let me know.

If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards

Denis

Blog: WhatTheFact.bi

Follow me: twitter.com/DenSelimovic

[MReinagl]

OK - but how can I display this now next to the data for the seven days, i.e. right of the columns shown in my screenshot at the top?