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'm trying to get a calculated column with MTD totals but hitting a wall and need help. Data mock up for my issue:
However, my code is duplicating the total for the day on each line:
MTDSales = VAR RowDate = SalesData[Date] RETURN CALCULATE ( SUM (SalesData[Sales]), FILTER ( SalesData, SalesData[Date] <= rowdate && SalesData[Agent] = earlier(SalesData[Agent]) ) )
Results in...
- My actual dataset does span multiple months, so will need logic to take into account the month
- I'm using a calculated column because I will need use these totals for a separate measure (i.e. track how far an agent is above goal day once he hits goal)
New to PBI, any help is appreciated.
Hi @connork,
In your data, same agent could have multiple sales per day, so when you calculate sumulative total for each agent, DAX will sum up the total sales for each day.
If you want to get the expected data, you need to have a autogrow Order column.
Column 2 = CALCULATE(SUM(Table4[Sales]),FILTER(ALLEXCEPT(Table4,Table4[Agent]),Table4[OrderNumber]<=EARLIER(Table4[OrderNumber])))
Regards,
Charlie Liao
Thanks for the feedback.
My example scenario was for ease of concept, but reality is closer to a production environment where the order has revenue earned over multiple days. Therefore the order number in my data is neither a unique identifier nor is it sequential (could get revenue on the order over multiple days from multiple agents); any idea how I can acheive something simliar to your example below with this in mind?
Right now my workaround has been to create a separate table that aggregates the daily revenue by agent; this makes the MTD revenue calculation work, however I no longer have the abiltiy to create a hierarchy of Agent>Order to allow the report receipient to drill-down for more info.
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 |
---|---|
113 | |
100 | |
78 | |
76 | |
52 |
User | Count |
---|---|
144 | |
109 | |
108 | |
88 | |
61 |