Year to date result for all customers that have full data

[Anonymous]

Hi

I have a model with three tables that are all linked. These tables are:

A standard date table:

Date

Jan 2020

Feb 2020

Mar 2020

A table with metadata for each customer:

Customer	Country	Data is ready until
A	USA	March 2020
B	Great Britain	March 2020
C	China	Feb 2020

And a table with sales by customer:

Date	Customer	Sales
Jan 2020	A	100
Jan 2020	B	200
Jan 2020	C	300
Feb 2020	A	100
Feb 2020	B	200
Feb 2020	C	300
March 2020	A	100
March 2020	B	200
March 2020	C	(blank)

The reason the result for customer C for March is blank, is because their data hasn’t become available yet. It will become available at a later stage.

I want to calculate the total sales year to date, which is currently being done like this:

Total sales year to date (January to March 2020)

Customer A = 100 + 100 + 100    = 300

Customer B = 200 + 200 + 200    = 600

Customer C = 300 + 300 + blank = 600

Total year to date sales in March = 300 + 600 +600 = 1500

I can do the same calculation for the year 2019. Back then we had the exact same amount of sales. But difference is that we did had sales for Customer C for the month of March back then.

Total sales year to date (January to March 2019)

Customer A = 100 + 100 + 100  = 300

Customer B = 200 + 200 + 200  = 600

Customer C = 300 + 300 + 300  = 900

Total year to date sales in March = 300 + 600 + 900 = 1800

This is leading confusing results. When I now compare the total 2020 sales (1500) VS the sales from 2019 (1800), the number shows a big drop, which would indicate we’re doing badly, but actually it’s just because customer C hasn’t finished their month yet.

As Customer C is not yet fully available, I only want to calculate my total sales based on customer A and B when I look at the year to date performance for March. When comparing year over year results, I then also only want to calculate the numbers for customer A and B. However, when looking at the year to date performance for February, I want to include all customers (as we have full data for all of them over that time period).

I’m looking for a measure that can do all of this. What should that measure look like? Or would I perhaps need an extra column in any of the tables to achieve this?

Best regards

Bas

[amitchandak]

@Anonymous , You can use time intelligence with date table

example

YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"12/31"))
Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"))

Power BI — Year on Year with or Without Time Intelligence

Please provide your feedback comments and advice for new videos
Tutorial Series Dax Vs SQL Direct Query PBI Tips
Appreciate your Kudos.

[Anonymous]

Hi @amitchandak ,

Thank you for you reply but this would simply give me the year to date sales. It does not take into account that some customers haven't finished their month yet.

Best regards

Bas

[amitchandak · Accepted solution]

@Anonymous , Divide by

CALCULATE(Distinctcount(Sales[Sales Month]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"))

multiply

month(maxX(allselected('Date'),'Date'[Date]))

Try

measure  =

var _m =month(maxX(allselected('Date'),'Date'[Date]))

return

divide(
YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"12/31")), CALCULATE(Distinctcount(Sales[Sales Month]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"))) *_m