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:

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.

2 Replies

  • 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.