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

DAX - get min and max value within a date range or a year

Hi @PowerBIDev @power

am new to power bi and just getting used to using measures , 

need your suggestion on how best to build Dax measures for the below scenario

E.g. I have sales per day for last 2 yrs and would like to calculate 2 things 

1. sales min and max during the last 1 year or selected date range basically show a range e.g. sales / day was from 100 items ( min) to 220/ day ( max ) 

2. Diff of sales done today  and same day last month 

 

I do have a calendar day linked to the sales table

 

Thanks in advance 

 

1 ACCEPTED SOLUTION
AllisonKennedy
Super User
Super User

I like to use DATEADD for same day last month. For your min/max over entire period you can simply do: 

 

Min Items = MIN(Sales[ItemsPerDay])

Max Items = MAX(Sales[ItemsPerDay])

Total Items = SUM(Sales[ItemsPerDay])

And then put them in a card visual with a date slicer on the page.

 

Items Last Month = CALCULATE([Total Items], DATEADD(Calendar[Date], -1, month))

and put that in a visual with Calendar[Date] along the axis and put both [Total Items] and [Items Last Month] in it.


Please @mention me in your reply if you want a response.

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

View solution in original post

3 REPLIES 3
Syndicate_Admin
Administrator
Administrator

Good day, I want to get the minimum and maximum of a column of dates but I want to do it only from a sample of data, that is to say all those whose id are the same take the minimum and maximum to know in this way which was the first sample taken, which the second and which the third, I hope you can help me. Thank you

AllisonKennedy
Super User
Super User

I like to use DATEADD for same day last month. For your min/max over entire period you can simply do: 

 

Min Items = MIN(Sales[ItemsPerDay])

Max Items = MAX(Sales[ItemsPerDay])

Total Items = SUM(Sales[ItemsPerDay])

And then put them in a card visual with a date slicer on the page.

 

Items Last Month = CALCULATE([Total Items], DATEADD(Calendar[Date], -1, month))

and put that in a visual with Calendar[Date] along the axis and put both [Total Items] and [Items Last Month] in it.


Please @mention me in your reply if you want a response.

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

Anonymous
Not applicable

Thanks for the reply
I was able to get the monthly measure working , however the min and max value for the year didn't quite get that . 
if i just wanted the max value across the date range , the above formula would have worked i.e. Min Items = MIN(Sales[ItemsPerDay]) but i want to get the min and max for within a date range that is last one year . 
based on the effective date i.e. today , the formual should get me a min and max value within the last 12 months .
i used the below formula AND GOT THIS WORKING . Many Thanks 
Items sold MAX/day Last 1 year  =
CALCULATE(MAX( [Sales[ItemsPerDay]),DATESINPERIOD(Calendar[Date],MAX(Calendar'[Date]),-1,YEAR))

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.