Calculate max of a month, per day.

[Niel]

Let's say I have a table like the below, so that the granularity is Datekey & ProductID.

Now I would like to get the highest sales per day, of the month.

The result for month 201807 should be 22 (that's the sales of 20180701) 

That's pretty straight forward to calculate when there are no filters by using

SalesDaily = CALCULATE(
sum(factRevenue[Sales])|FILTER(
factRevenue|(factRevenue[DateKey]=EARLIER(factRevenue[DateKey]))
))

However if I were to then filter on ProductID = 1 for example the above wouldn't work, since this calculation creates a column on the dataset (which happens before the interactions happen, so no filters are applied).

Hope that makes sense?

[MFelix]

Hi @Niel,

Not sure if I understand your question, the formula you present is for a calculated column or measure?

If you want to calculate the maximum sales per day you just need to make the measure 

Sales Daily = SUM(Table[Sales])

Measure are calculated on context so if you only add the day it wil give you 22 for 20180701 and so on, when adding slicers or filter to your visual or report this will affect the context of your measure and calculated it according to that data so you will get 10  when you select the Product 1.

Can you share some more insights on what is your doubt?

Regards,

MFelix

[Niel]

Hi, 

Thanks for the reply. Apologies if I wasn't clear.

The function I gave is for a column.

As I mentioned, I would like to get the highest sales per day, of the month.

So the highest sales day in July would be 22

When filtering on productid = 1, the highest day for the month would be 14.

These are the numbers to be shown in the table when grouping by yearmonth instead of by date.

[v-jiascu-msft]

Hi @Niel,

The calculated column won't response to the slicer in the report. So if the value is 22, it will always be 22 unless the data source changes. Did you try the @MFelix's solution? Create a measure instead.

Best Regards,

Dale

[Niel]

Hi Dale,

No, MFelix didn't read my question correctly, so he's not answering my question at all.

I'll just accept this as another Power BI limitation and just create a seperate dataset to calculate this.

Thanks,

Niel

[MFelix]

Hi @Niel,

In Power BI as a good practice you should create measures instead of calculated columns.

Looking at your needs, if you make a calculated column you will have issues on slicing the information. Becasue if you calculate the max of sales for a month your column will be with a single value per month, in your example 22, if you want to add the product then the value will be 14, and you only be abble to get 1 product, adding variations of this to the calculated colum you will get the same values as in column sales.

This is a perfect case to create a measure since based on context the result you need is different, try the @v-jiascu-msft solution and tell if it works.

Regards,

MFelix

[v-jiascu-msft · Accepted solution]

Hi Niel,

It should be a measure in your scenario. Please try the demo in the attachment.

Measure =
MAXX (
    SUMMARIZE ( 'Table1', 'Table1'[Datekey], "total", SUM ( Table1[Sales] ) ),
    [total]
)

Best Regards,

Dale

[Niel]

Awesome, thank you so much. Works perfectly