Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I want a dax code to return the highest value of previous sales on a monthly basis. E.g see below
Month | Sales This month | Highest ever sales |
Jan | 1,000 | 1,000 |
Feb | 0 | 1,000 |
March | 2,000 | 2,000 |
In the above table, when in January sales this month is 1000 and the highest ever was 1,000 and in February our highest sales ever still remains 1000 despite selling nothing for that month. However, in March the figure changed because the march sales has beaten that of January. Please I need a DAX to return this.
@amitchandak @VahidDM @AlexisOlson
Solved! Go to Solution.
Hi @abimfol1
Please try the following Measure.
higest previous order mth =
VAR midT =
TOPN (
1,
FILTER (
SUMMARIZE (
ALL ( DimTable ),
DimTable[Month],
DimTable[MonthYearNumber],
"val", SUMX ( DimTable, [#Orders] )
),
DimTable[MonthYearNumber] <= MIN ( DimTable[MonthYearNumber] )
),
[val], DESC
)
return MAXX ( midT, [val] )
Then, the result should look like this.
For more details, please refer to the attached pbix file.
Best Regards,
Community Support Team _ Caiyun
If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!
Hi @abimfol1
Please try the following Measure.
higest previous order mth =
VAR midT =
TOPN (
1,
FILTER (
SUMMARIZE (
ALL ( DimTable ),
DimTable[Month],
DimTable[MonthYearNumber],
"val", SUMX ( DimTable, [#Orders] )
),
DimTable[MonthYearNumber] <= MIN ( DimTable[MonthYearNumber] )
),
[val], DESC
)
return MAXX ( midT, [val] )
Then, the result should look like this.
For more details, please refer to the attached pbix file.
Best Regards,
Community Support Team _ Caiyun
If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!
Hi @abimfol1
May I know whether your issue has been resolved? If you still have problem on it, could you please show me your #Orders Measure and let me know how many tables in your model and the relationships among them? Thanks in advance!
Best Regards,
Community Support Team _ Caiyun
If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If you still have problems on it, please feel free to let us know. Thanks a lot!
Thanks for your response. this is my my order measure
Hi @abimfol1 ,
Is Sales a measure or values in your table.
Also, can you share sample data and data model ( Which table does the store value come from).
Regards,
Harsh Nathani
Its a measure . it is a distinct count of order number. Problem is i kind of have a dax code that works but any months that the order count is zero then it doesnt return corresponding value of the highest previous month
@abimfol1 you can either create a calculated column like this
Column =
var _monthIndex = Calculate(MAX('Table'[MonthIndex]))
return MAXX(FILTER(ALL('Table'),'Table'[MonthIndex]<=_monthIndex),'Table'[Sales])
or a measure like this
Measure =
var _monthIndex = MAX('Table'[MonthIndex])
return MAXX(FILTER(ALL('Table'),'Table'[MonthIndex]<=_monthIndex),'Table'[Sales])
thanks for your response. it didnt work for me. when i drag in each store
Whenever i have 0 as a value for a month for each customer then i dont get the highest previous month using my own solution. However if the currenth month sales is greater than zero then it works . See below
@abimfol1 any chance you can post the sample pbix?
@abimfol1 provide sample pbix or put the screenshot of the error here.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
44 | |
21 | |
20 | |
15 | |
13 |
User | Count |
---|---|
45 | |
41 | |
39 | |
19 | |
19 |