Issues with Lost Customer Sales DAX calculation and request for DAX expressions to calculate increas

[Dhi_Qwe]

Tags: #DAX #SalesData #LostCustomerSales #AggregateLevel #IncreasedSales #DecreasedSales #PowerBI

Mentions: @amitchandak @MiguelMartinez @Greg_Deckler @ImkeF @parry2k @ankitpatira @Melissa @brianj 

Hi everyone,

I am working on a sales data report in Power BI and have the following table and join:

In the "sales_export_tool_all" table:

• material: Data format in String - It has material number.
• invoiced_net_value_usd: Data format number - It has sales revenue values.
• customer_name_num: Data format in String - It has customer name.
• ledger_id: Data format in String - It has region code.
• invoiced_qty: Data format in number - It has sales quantity.
• invoiced_date2: Data format in Calendar - It has the 1st date of every month in which sales were made.

In the "Calendar" table:

• Date: Format in short date.
• Year: Format in number.
• Month: Format in Text.

I have created the following DAX expression for Lost Customer Sales:

Lost Customer Sales =
VAR CustomerSalesCY = CALCULATE([Current Period Sales])
VAR CustomerSalesPy =
CALCULATE(
[Current Period Sales],
ALL('Calendar'),
'Calendar'[Year] = SELECTEDVALUE('Calendar'[Year]) -1
)
RETURN (
IF(
CustomerSalesCY <> BLANK() && CustomerSalesPy = BLANK(),
0,
IF(
CustomerSalesCY <> BLANK() && CustomerSalesPy <> BLANK(),
0,
-[CustomerSalesPy]
)
)
)

While this calculation works correctly at a row level, it's not producing the expected results when aggregated. Can anyone suggest how to fix this issue?

Also, I would appreciate some help in creating DAX expressions for the following

1.Increased sales amount from existing customers.

2.Decreased sales amount from existing customers.

I will be using Date from the Calendar table, material, and ledger_id as filters in my dashboard.

Thanks in advance for your help!

[Dhi_Qwe]

@lbendlin @mahoneypat 

Pls help. 

[lbendlin]

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-...
Please show the expected outcome based on the sample data you provided.

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

[Dhi_Qwe]

Mentions: @amitchandak @MiguelMartinez @Greg_Deckler @ImkeF @parry2k @ankitpatira @Melissa @brianj 

Need help

[Dhi_Qwe]

@Greg_Deckler :Thanks for your revert. I tried Lost Customer Sales2 with SumX but I still have blank rows. Not sure how to handle it. Can you please help?

PS - Thanks for sharing the vote link - That feature will be superuseful 🙂 

[Dhi_Qwe]

I tried creating one more Dax, with SUMX function, but strangely it shows blank where I was expecting -[CustomerSalesPy] . 

Lost Customer Sales2 =

SUMX(

VALUES('sales_export_tool_all'[customer_name_num]),

IF(

CALCULATE([Current Period Sales], 'sales_export_tool_all'[customer_name_num] = EARLIER('sales_export_tool_all'[customer_name_num])) <> BLANK() &&

CALCULATE([Current Period Sales], ALL('Calendar'), 'Calendar'[Year] = SELECTEDVALUE('Calendar'[Year]) -1, 'sales_export_tool_all'[customer_name_num] = EARLIER('sales_export_tool_all'[customer_name_num])) = BLANK(),

0,

IF(

CALCULATE([Current Period Sales], 'sales_export_tool_all'[customer_name_num] = EARLIER('sales_export_tool_all'[customer_name_num])) <> BLANK() &&

CALCULATE([Current Period Sales], ALL('Calendar'), 'Calendar'[Year] = SELECTEDVALUE('Calendar'[Year]) -1, 'sales_export_tool_all'[customer_name_num] = EARLIER('sales_export_tool_all'[customer_name_num])) <> BLANK(),

0,

-[CustomerSalesPy]

)

)

)

[Greg_Deckler]

@Dhi_Qwe First, please vote for this idea: https://ideas.powerbi.com/ideas/idea/?ideaid=082203f1-594f-4ba7-ac87-bb91096c742e

This looks like a measure totals problem. Very common. See my 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

Also: https://youtu.be/uXRriTN0cfY
And: https://youtu.be/n4TYhF2ARe8