Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Highest value for the Sales month

HI,

I have the sales month starting from Nov-OCT.

I want to get the Highest count among these months starting last Sales year 2019 and current Sales Year2020

Here is the matrix from power bi.

Year Month New Count Lines
2020 NOV 4
  DEC 5
  JAN 4
  FEB 5
  APR 1
  MAY 12
  JUN 10
2019 NOV 5100
  AUG 10
  SEP 181
  OCT 15
  FEB 17
  MAR 31
  APR 12
  MAY 20
  JUN 24
  JUL 12
2018 AUG 7297
  SEP 9462
  OCT 10734
  JUL 7626

 

My output should be 5100, as this is the highest count from 2019 to 2020.

 

I tried to use this Measure but it gives the highest including the 2018 year aswell. Un able to fix this with Time intelligence.

Any help adjusting this formula?

 

Measure=

Var BE=Minx(SUMMARIZE('Table','Table'[Sales Month],"BE",[New Count Lines]),[BE])
Return 
BE
 
Thanks,
Tejaswi
 
1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

@Anonymous , Try one of the two. First one assume there is filter for 2019, 2020

 


maxx(summarize(table, table[year],table[month], "_1",[New line count]),[_1])

maxx(summarize(filter(table, Table[Year]>=2019), table[year],table[month], "_1",[New line count]),[_1])

View solution in original post

6 REPLIES 6
Anonymous
Not applicable

Try this Measure:

MaxMeasure = Calculate(Max(CntTable[Count]), FILTER(CntTable,CntTable[Year]=2020||CntTable[Year]=2019))
 
("2020" and "2019") if the year is data type of text
 
HTH,
Smitty
Tahreem24
Super User
Super User

@Anonymous ,

Try the below measure:

Measure = CALCULATE(MAX(Sheet4[New Count Lines]),DATESBETWEEN(Sheet4[DATE],DATE(2019,1,1),DATE(2020,12,31)))
It gives 5100.
 
Don't forget to give thumbs up and accept this as a solution if it helped you!!!

Please take a quick glance at newly created dashboards : Restaurant Management Dashboard , HR Analytics Report , Hotel Management Report, Sales Analysis Report , Fortune 500 Companies Analysis , Revenue Tracking Dashboard

@Anonymous 

Try the below measure:

Measure = CALCULATE(MAX(Sheet4[New Count Lines]),DATESBETWEEN(Sheet4[DATE],DATE(2019,1,1),DATE(2020,12,31)))
It gives 5100.
Capture.PNG
 
Don't forget to give thumbs up and accept this as a solution if it helped you!!!

Please take a quick glance at newly created dashboards : Restaurant Management Dashboard , HR Analytics Report , Hotel Management Report, Sales Analysis Report , Fortune 500 Companies Analysis , Revenue Tracking Dashboard
Anonymous
Not applicable

Thanks All for your reply

 

Hi @Tahreem24 , Your solution would have worked if New count lines is a column. Mine is a Measure.

 

Hi @amitchandak   your solutions works for me.

 

 

Thanks,

Tejaswi

 

amitchandak
Super User
Super User

@Anonymous , Try one of the two. First one assume there is filter for 2019, 2020

 


maxx(summarize(table, table[year],table[month], "_1",[New line count]),[_1])

maxx(summarize(filter(table, Table[Year]>=2019), table[year],table[month], "_1",[New line count]),[_1])

Anonymous
Not applicable

You can try filtering out the year from filter on this visual or filter on this page or use slicer to get the required output.
I did not understand your question as  much, correct me if I understood it wrong.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.