Forum Discussion

connork's avatar
connork
Frequent Visitor
9 years ago

MTD Total Column with Multiple Records per Day

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: MTDSa...
v-caliao-msft's avatar
v-caliao-msft
Icon for Microsoft Employee rankMicrosoft Employee
9 years ago

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

connork's avatar
connork
Frequent Visitor
9 years ago

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.