topic Re: Sales Variation : when the client is created after the selected month, show 0 instead of -100% in DAX Commands and Tips

Sales Variation : when the client is created after the selected month, show 0 instead of -100%

Sofinobi — Tue, 13 Jun 2023 15:59:21 GMT

hi all,

please i need your help,

i have a measure to calculate the sales variation by month by Client like this:

Sales Variation = DIVIDE([Sales],

CALCULATE([Sales],DATEADD(Date[Date],-1,MONTH)),2)-1
all is working great, but i want to show 0 instead of -100% when the client is created after the selected month
eg : Client created on June, so the variation on may, April and march should be 0 not -100%

Re: Sales Variation : when the client is created after the selected month, show 0 instead of -100%

tamerj1 — Wed, 14 Jun 2023 13:30:50 GMT

Hi Sofinobi
Please try

Sales Variation = VAR Percentage = DIVIDE ( [Sales], CALCULATE ( [Sales], DATEADD ( Date[Date], -1, MONTH ) ), 2 ) RETURN COALESCE ( Percentage, 1 ) - 1

Re: Sales Variation : when the client is created after the selected month, show 0 instead of -100%

Sofinobi — Wed, 14 Jun 2023 14:58:44 GMT

thank you so much for your answer, but it replaces all the -100% by 0 even if the first one is correct,
eg: in case of client is created on january (with sales) and on february he was with no sales, so the variation should stay -100% for february, not 0.

thank you

Re: Sales Variation : when the client is created after the selected month, show 0 instead of -100%

johnt75 — Wed, 14 Jun 2023 15:54:09 GMT

Try

Sales Variation = VAR MaxDate = MAX ( 'Date'[Date] ) RETURN IF ( SELECTEDVALUE ( 'Client'[Date created] ) > MaxDate, DIVIDE ( [Sales], CALCULATE ( [Sales], DATEADD ( Date[Date], -1, MONTH ) ), 2 ) - 1, 0 )

Re: Sales Variation : when the client is created after the selected month, show 0 instead of -100%

Sofinobi — Thu, 15 Jun 2023 08:19:27 GMT

thank you so much for the answer, but the result gives me only 0 or -100%
in your VAR MaxDate, i think it will return Today

thank you

Re: Sales Variation : when the client is created after the selected month, show 0 instead of -100%

johnt75 — Thu, 15 Jun 2023 08:46:45 GMT

Its meant to be used in a visual which has columns from your date table, I would imagine it would be the year month column. MaxDate would then pick up the end of that month.

Re: Sales Variation : when the client is created after the selected month, show 0 instead of -100%

Sofinobi — Thu, 15 Jun 2023 10:54:56 GMT

this is the visual that i'm using

Re: Sales Variation : when the client is created after the selected month, show 0 instead of -100%

johnt75 — Thu, 15 Jun 2023 11:13:37 GMT

Check the created date for the clients in question. If they were created before the dates you are showing but had no sales then that would explain what you are seeing.

Re: Sales Variation : when the client is created after the selected month, show 0 instead of -100%

Sofinobi — Thu, 15 Jun 2023 12:12:43 GMT

no, the creation date for this Client is after the dates in the matrix (created in june), that is my problème

Re: Sales Variation : when the client is created after the selected month, show 0 instead of -100%

johnt75 — Thu, 15 Jun 2023 12:39:31 GMT

Does the column in your visual which identifies the client come from the same table which holds the created date? If it doesn't then that could be the problem.

Re: Sales Variation : when the client is created after the selected month, show 0 instead of -100%

Sofinobi — Thu, 15 Jun 2023 14:04:19 GMT

the column in my visual comes from my Date table, not from Client table, may be that the problème, i'll check that