topic Re: Measures/calculated columns to show max value with date, and latest date with value. in DAX Commands and Tips

Measures/calculated columns to show max value with date, and latest date with value.

Anonymous — Mon, 28 Sep 2020 09:05:47 GMT

Hi I have a sample scenerio data set(actual data set used has thousands of rows):

Location	Date	Item	% of total of the day
East	1/1/2012	Burger	80
West	1/1/2012	Burger	40
North	1/1/2012	Burger	30
East	1/1/2012	Fries	20
West	1/1/2012	Fries	60
North	1/1/2012	Fries	70
East	2/1/2012	Burger	40
West	2/1/2012	Burger	50
North	2/1/2012	Burger	30
East	2/1/2012	Fries	60
West	2/1/2012	Fries	50
North	2/1/2012	Fries	70

So i have 2 slicers to select the menu item and location. And i want to add card/table to show for example:

Slicer option selected: Burger and East.

Results shown:

Latest Date	2/1/2012
% of the total of the day	40

Date of Max % of the total of the day	1/1/2012
Max % of the total of the day	80

If understand what this means, it's basically to monitor the latest date for the selected item(due note there are hundreds of items and some items are not regular items) and location, together with the value of % of the total of the day;

And to see which date have the highest % of the total of the day together with max % value.

So any ideas/ measures/calculated columns to help with creating a card/table to show the above results?

Re: Measures/calculated columns to show max value with date, and latest date with value.

stevedep — Tue, 29 Sep 2020 05:38:51 GMT

This would do the trick:

___IsHighestOftheDay = var _tblSum = SUMMARIZE('Table','Table'[Date]//,'Table'[Item],'Table'[Location] ,'Table'[% of total of the day], "H", CALCULATE(MAX([% of total of the day]), ALLEXCEPT('Table',//'Table'[Location], 'Table'[Date]))) return COUNTROWS(FILTER(_tblSum,[% of total of the day]=[H])) __LatestDate = CALCULATE(MAX([Date]),ALLEXCEPT('Table','Table'[Location],'Table'[Item])) ___LatestDateWithHighestPosition = CALCULATE(MAX([Date]), FILTER(ALLEXCEPT('Table','Table'[Location],'Table'[Item]),[___IsHighestOftheDay]=1)) ___LatestPercValithHighestPosition = CALCULATE(MAX([% of total of the day]), FILTER(ALLEXCEPT('Table','Table'[Location],'Table'[Item]),[___IsHighestOftheDay]=1)) ___LatestDateWithHighestPosition = CALCULATE(MAX([Date]), FILTER(ALLEXCEPT('Table','Table'[Location],'Table'[Item]),[___IsHighestOftheDay]=1))

As seen here:

File is available here.

pls mark as solution if so. Thumbs up for the effort is appreciated.

Kind regards,

Steve.

Re: Measures/calculated columns to show max value with date, and latest date with value.

Anonymous — Tue, 29 Sep 2020 03:46:16 GMT

Hi , thank you for the reply, but I don't think this is the outcome I would want. To make it clearer, let me re explain,

I have two slicers, one for the item and another for the location.

For example my Slicer option selected: Burger and East.

I want to have a way to show these results:

Latest Date	2/1/2012
% of the total of the day	40

Date of Max % of the total of the day	1/1/2012
Max % of the total of the day	80

I would like to be able to select the menu and the location, and see when was the latest date that menu item was sold and how much % was it sold on that day. And in addition, also be able to see in the past history the date which had that menu item having the highest % of the day amoung all the other dates, so i can know whats the highest % I've ever sold on a day for that item and at that location.

Burger and fries are a simplified version of the actual data, the actual data has thousand of rows with years of data, each day there are like 20 items. And every day the items change. Hope this clarifies.

Re: Measures/calculated columns to show max value with date, and latest date with value.

stevedep — Tue, 29 Sep 2020 05:34:48 GMT

Anonymous , I was a bit to quick with my initial response. I updated my response, pls take a look and see if this works for you.

Kind regards, Steve.

Re: Measures/calculated columns to show max value with date, and latest date with value.

Anonymous — Fri, 02 Oct 2020 01:46:53 GMT

Hi sorry for the late reply, the solution presented has helped solved part of this proble. I found a simpler solution butit is not completed.
measure for Max % of the total of the day:
Max % of the total of the day = CALCULATE(MAX('data'[% of total of the day]),ALLEXCEPT('data','data'[location],'data'[item]))

measure for Date of Max % of the total of the day:
Date of Max % of the total of the day =LOOKUPVALUE('data'[Date],'data'[% of total of the day],[Max % of total of the day])

measure for latest date:
Latest Date = CALCULATE(LASTDATE('data'[date]),ALLEXCEPT('data','data'[item],'data'[location]))

But for the corresponding value which is % of the total of the day, i thought I could use the same measure(lookupvalue) as Date of Max % of the total of the day, but it gave me an error, this was the DAX I used:
% of the total of the day for latest date = LOOKUPVALUE('data'[% of the total of the day],'data'[date],[Latest Date])
So I'm now focusing on solving this last problem, I started a new thread focusing more on it with more information of the scenerio:, link is below:
Last value of a calculated column based on slicer options.