Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to Solution.
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.
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
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
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.
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
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))
User | Count |
---|---|
127 | |
108 | |
99 | |
63 | |
62 |
User | Count |
---|---|
136 | |
113 | |
102 | |
71 | |
60 |