topic Last value of a calculated column based on slicer options. in DAX Commands and Tips

Last value of a calculated column based on slicer options.

Anonymous — Thu, 01 Oct 2020 08:31:01 GMT

Hi I need help with forming a measure.

My scenerio:

Location	Date	Item	quantity sold	Total item sold for the day	%of the total sold for the day
East	1/1/2020	Burger	50	100	50
West	1/1/2020	Burger	40	100	40
East	1/1/2020	Fries	50	100	50
West	1/1/2020	Fries	60	100	60
East	2/1/2020	Burger	80	100	80
West	2/1/2020	Burger	70	100	70
East	2/1/2020	Fries	20	100	20
West	2/1/2020	Fries	30	100	30
East	3/1/2020	Burger	65	100	65
West	3/1/2020	Burger	75	100	75
East	3/1/2020	Soda	35	100	35
West	3/1/2020	Soda	25	100	25

The total item sold for the day column and %of the total sold of the day are calculated columns.

their DAX are

Total item sold for the day = Calculate(SUM(('data'[Quantity sold]),ALLEXCEPT('data','data'[location],'CD3,4,5'[Date]))

%of the total sold of the day = DIVIDE('data'[Quantity sold],'data'[Total item sold for the day])

I have 2 slicers, one controlling the Item, and the other controlling the location.

slicer option seletec: Item = Fries, Location = West

I have a measure created for latest date

Latest Date = CALCULATE(LASTDATE('data'[Date]),ALLEXCEPT('date','data'[Item],'data'[location])

Now I need another measure to show the cell for %of the total sold for the day for that latest date.

So i can have the following table visual:

Latest Date	%of the total sold for the day
2/1/2020	30

I'm having trouble coming up with the measure.

I tried

LOOKUPVALUE('data'[% of total sold for the day],'data'[Date], [Latest Date])

but it gave me an error, I think it is because the column % of total sold for the day is a calculated column.

Tried lastnonblankvalue too but my %of the total sold for the day is a column not a measure.

Do you guys have any measure/DAX/ways/ideas so that I am able to see the most recent value based on teh slicer options?

Re: Last value of a calculated column based on slicer options.

amitchandak — Thu, 01 Oct 2020 09:12:09 GMT

Anonymous , Try like

divide(CALCULATE(lastnonblankvalue('data'[Date],sum(table[Total item sold for the day])),ALLEXCEPT('date','data'[Item],'data'[location])),calculate(sum(table[Total item sold for the day]) ,all(table)))

Re: Last value of a calculated column based on slicer options.

stevedep — Thu, 01 Oct 2020 12:05:19 GMT

Anonymous , Kindly let us know if this posts is answered:

https://community.powerbi.com/t5/DAX-Commands-and-Tips/Measures-calculated-columns-to-show-max-value-with-date-and/m-p/1400815#M25779

Re: Last value of a calculated column based on slicer options.

Anonymous — Fri, 02 Oct 2020 02:34:38 GMT

Hi thank you for reply but when I implemented it, it gave 0% for all slicer options.
I really think the answer is
LOOKUPVALUE('data'[%of the total sold for the day],'data'[date],[Latest Date])

But it shows ' cant display the visual' for all visuals

Re: Last value of a calculated column based on slicer options.

Anonymous — Thu, 08 Oct 2020 01:48:08 GMT

Thank you for help so far, but I already found an answer by myself already, which is to use the KPI visual, with '% of the total of the day' as the indicator and 'Date' as the trend axis. The inidcator shows the latest value of the % of the total, with location slcier and menu item slicer selected an option each.