Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I'm trying to create a "late order value" metric that is forecasted over time, but I can't figure out the measure.
There are 2 tables (Dates, Orders); they are not joined.
The visual is a bar-chart with the DateMonth on the X-Axis. The dates table is complete, so min(DateDay) and max(DateDay) would be 1/1/2020 and 1/31/2020, respectively for the X-Axis category that corresponds to January of this year.
The formula for "LateOrders" would be something like this:
sum(
{$<OrderDueDate={"<$(=max(DateDay))"},OrderCommitDate={">$(=max(DateDay))"}>}
OrderValue)
The problem is that this seems to evaluate at the chart level, not for each individual month. If I run this calculation, it shows the same data in each month, calculated based on the last value in the chart (in this case 12/31/2020). How do I rewrite this to calculate for each X-Axis Cateogry in the chart?
Any help would be greatly appreciated!
Thanks
Thanks, I've got a 90% solution using what you proposed. The only downside to this approach is that it will require a new measure if I want the X-Axis to be "Year", "Week", or "Day". I can live with it; at least I don't have to duplicate data.
sum({$<OrderValue={"=[OrderDueDate]<=[ShipDate]"}>} if(OrderDueDate<=DateMonthEnd and ShipDate>DateMonthEnd ,OrderValue))
Thanks for the help.
Update July 1, 2020: you can use this method in conjunction with GetObjectDimension() to make one measure that works with multiple timescales. Note that GetObjectDimension will return the displayed dimension label, not necessarily the dimension name.
if I understand correctly.
you can add flag in your data model, because :
so it normal that it shows the same data in each month
You could use Sum(If(... instead, that should give you the expected output. If you need more specific help, try to share a sample app to work on.
Thanks, I've got a 90% solution using what you proposed. The only downside to this approach is that it will require a new measure if I want the X-Axis to be "Year", "Week", or "Day". I can live with it; at least I don't have to duplicate data.
sum({$<OrderValue={"=[OrderDueDate]<=[ShipDate]"}>} if(OrderDueDate<=DateMonthEnd and ShipDate>DateMonthEnd ,OrderValue))
Thanks for the help.
Update July 1, 2020: you can use this method in conjunction with GetObjectDimension() to make one measure that works with multiple timescales. Note that GetObjectDimension will return the displayed dimension label, not necessarily the dimension name.