Calculate Sum for Latest Available Date (As of Existing Date)

[soldstatic]

Can anybody help me wrap my head around the best way to calculate a metric so that it doesn't change for past data over time? Meaning whenever someone looks at the report for past data, it always calculates the same?

The KPI is Customer Interruptions / Customers Served. I'm using the following measures:

Customers Measure = 
CALCULATE(
    SUM('TableServedByDate'[Premises])
    ,LASTDATE('TableServedByDate'[CALENDAR_DATE])
)


KPI = sum('EventList'[CI])/[Customers Measure]

The problem is the total is using the Customers Measure with the latest count, it isn't summing up each of the previous entries. Meaning if I look at the whole year, it sums up the CI just fine but it divides by say December's customer count. It looks good on a month by month view, since I only get the Premises data once a month, but I want to use this count for wherever it's applicable but then NOT recalcualte it afterwards. Does that make sense?

[v-lili6-msft · Accepted solution]

hi, @soldstatic 

This looks like a measure totals problem. Very common. See this post about it here: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376

Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907

If not your case, please share some sample data and expected out.

Best Regards,

Lin

[soldstatic]

OK so those posts really helped me for scaling up in time, ie going from month to year etc. However, I'm struggling to adapt it to the other direction. Meaning: I need to use the most recent customer count available, even if there isn't one joined on this particular date.

The date table and the customer count table are joined on calendar_date, because I might get a count on the 1st of the month or I could get a count on the 15th, or every day of the month. So what we want to do is use the most recent count as of the date_table date (which is what is on the axis of the visualizations).

So basically this is what I'm getting right now for the measure above and I need to fill in the gaps:

Year	Month	CALENDAR_DATE	Customer Measure
2016	1	1/1/2016 0:00	34991
2016	1	1/3/2016 0:00
2016	1	1/4/2016 0:00
2016	1	1/6/2016 0:00
2016	1	1/7/2016 0:00
2016	1	1/9/2016 0:00
2016	1	1/10/2016 0:00
2016	1	1/11/2016 0:00
2016	1	1/12/2016 0:00
2016	1	1/13/2016 0:00
2016	1	1/14/2016 0:00
2016	1	1/18/2016 0:00
2016	1	1/19/2016 0:00
2016	1	1/20/2016 0:00
2016	1	1/23/2016 0:00
2016	1	1/25/2016 0:00
2016	1	1/28/2016 0:00
2016	1	1/29/2016 0:00
2016	1	1/30/2016 0:00
2016	2	2/1/2016 0:00	35001
2016	2	2/2/2016 0:00
2016	2	2/3/2016 0:00
2016	2	2/4/2016 0:00
2016	2	2/7/2016 0:00
2016	2	2/8/2016 0:00
2016	2	2/9/2016 0:00
2016	2	2/10/2016 0:00
2016	2	2/11/2016 0:00
2016	2	2/12/2016 0:00
2016	2	2/13/2016 0:00
2016	2	2/14/2016 0:00
2016	2	2/15/2016 0:00
2016	2	2/16/2016 0:00
2016	2	2/17/2016 0:00
2016	2	2/18/2016 0:00
2016	2	2/20/2016 0:00
2016	2	2/22/2016 0:00
2016	2	2/23/2016 0:00
2016	2	2/24/2016 0:00
2016	2	2/26/2016 0:00
2016	2	2/28/2016 0:00
2016	2	2/29/2016 0:00

[v-lili6-msft]

hi, @soldstatic 

Do you mean that you want fill in every month? If so, you could try TOTALMTD Function.

If you still have the problem, please share some some sample data and expected output as below:

Please see this post regarding How to Get Your Question Answered Quickly:
https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

Best Regards,

Lin

[soldstatic]

Thanks! Sometimes I just don't know what to search for, I appreciate the help. Looks like this should get me on the right track. I might come back if I can't figure it out though 😉 lol