look up value within temporary groupby table

[Bokchoy]

Hi all,

I have a temp table that sums up total_charge, grouped by single_date. Now i want to return the date where the highest "sum all charge" was hit.

im having troubles with referencing the "Date" column within my temp table and im unable to complete the measure.

Please help, thanks.

Highest Daily_date=

var temp1 =  GROUPBY(Jobs, Jobs[job_date] , "Sum all Charge", SUMX(CURRENTGROUP(), Jobs[total_charge]))

var maxcharge_date = "                               " 

return maxcharge_date

[Bokchoy]

Hi Ryan,

Thanks for looking into this, not sure if I explained it correctly.

I made a simple data set below.
https://drive.google.com/file/d/14MBKPOsPqzPb5mFZBV0LOBS56yE_0RtX/view?usp=sharing

[Bokchoy]

Hi Ryan,

Thanks for looking into this, not sure if I explained it correctly.

I made a simple data set below.
https://drive.google.com/file/d/14MBKPOsPqzPb5mFZBV0LOBS56yE_0RtX/view?usp=sharing

[ryan_mayu · Accepted solution]

@Bokchoy 

is this what you want?

maxdate = 
VAR tbl=SUMMARIZE(sample_jobs,sample_jobs[job_date],"charge",sum(sample_jobs[total_charge]))
VAR _max= maxx(tbl,[charge])
return maxx(FILTER(tbl,[charge]=_max),'sample_jobs'[job_date])

month = FORMAT([maxdate],"mmm")

[Bokchoy]

Hi ryan,

thanks for helping!

i was able to get what i needed from your code, though i used the Groupby function instead of summarize. The return maxx line was that i was looking for. 

again thanks heaps.

Record Daily date = 

#maxdate = 
#VAR tbl=SUMMARIZE(jobs,Jobs[job_date],"charge",sum(jobs[total_charge]))
#VAR _max= maxx(tbl,[charge])
#return maxx(FILTER(tbl,[charge]=_max),Jobs[job_date])

var temp1 = GROUPBY(Jobs, Jobs[job_date] , "Sum all Charge", SUMX(CURRENTGROUP(), Jobs[total_charge]))
var High_Daily = MAXX(temp1, [Sum all Charge])
var date_ = MAXX(FILTER(temp1, [Sum all Charge] = High_Daily), Jobs[job_date])

return date_

[ryan_mayu]

you are welcome

[ryan_mayu]

@Bokchoy 

could you pls proivde the sample data and expected output?