Issue in Comparing Prior Period Sales

[Prathyush]

Hi Everyone,

I am new to Power BI , I Need an assistance in Comparing the previous Period Sale with Last To last Month Sale. 

In my Scenario If current monthis april,  user has to take prvious month Sale and Compare it with selected periods prior to March.

My Last month Sale is  Working Fine

Last Period Sale = CALCULATE(SUM('fact SalesInvoice'[SalesAmountBase])+SUM('fact SalesInvoice'[DiscountAmountEndBase]),FILTER('dim Date','dim Date'[rank]='dim Date'[CurrentMonthRank]-1 ))

-->  I used a Rank Column for Month-Year  using Rankx for Ranking the Prior Months

My selected period Sale is working Fine

Selected Period Sale = CALCULATE(SUM('fact SalesInvoice'[SalesAmountBase])+SUM('fact SalesInvoice'[DiscountAmountEndBase]),FILTER('dim Date','dim Date'[rank]<='dim Date'[CurrentMonthRank]-2 && 'dim Date'[rank]>=('dim Date'[CurrentMonthRank]-1)-[Selected Period]))

Now I pulled Selected Period Sale and customers to a bar chart and a table, and put visual level filter that Last Period Sale <=0 to find Lost Customers in Last Period Compared to selected Period.

My Report is also looking Fine.

But When I Add Product Code Also as a Row in Table Visual, It shows Another List Of Customer which are not lost Customers.

Please see the below results Which is wrong.

 

Urgent Assistance is Appreciated....

Thanks

Prathyush

[v-qiuyu-msft]

Hi @Prathyush,

Is [Product Code] field comes from the same table "fact SalesInvoice"? Would you please share some sample data? If possible please share your .pbix file for our analysis.

Best Regards,
Qiuyun Yu

[Prathyush]

Hi Qiuyun,

Yes it is from fact Sales invoice.Please find samle data from fact sales invoice.

SellToCustomerID	BillToCustomerID	DocumentSalesID	DocumentSalesLineID	ChargesID	FinancialDimension1ID	FinancialDimension2ID	FinancialDimension3ID	FinancialDimension4ID	FinancialDimension5ID	FinancialDimension6ID	FinancialDimension7ID	FinancialDimension8ID	FinancialDimension9ID	FinancialDimension10ID	FinancialDimension11ID	FinancialDimension12ID	FinancialDimension13ID	FinancialDimension14ID	FinancialDimension15ID	ItemDimension1ID	ItemDimension2ID	ItemDimension3ID	ItemID	InventoryDimensionID	InventoryBatchID	InventorySerialID	InventorySizeID	PostingDateID	ReasonCodeID	EmployeeID	CommissionSalesGroupID	ReturnReasonCodeID	CurrencyID	GlobalDocumentNoID	GeneralTypeID	ProcurementCategoryID	ItemLedgerEntryNo	ValueEntryNo	DocumentType	InvoicedQuantity	ShippedQuantity	SalesAmountPosted	DiscountAmountPosted	LineDiscountAmountPosted	DiscountPercentAmountPosted	CostAmountPosted	CommissionAmountPosted	MiscChargesPosted	SumTaxPosted	SalesAmountBase	DiscountAmountBase	LineDiscountAmountBase	DiscountPercentAmountBase	CostAmountBase	CommissionAmountBase	MiscChargesBase	SumTaxBase	SalesAmountACY	DiscountAmountACY	LineDiscountAmountACY	DiscountPercentAmountACY	CostAmountACY	CommissionAmountACY	MiscChargesACY	SumTaxACY	DiscountAmountEndPosted	MiscChargesEndPosted	DiscountAmountEndBase	MiscChargesEndBase	DiscountAmountEndACY	MiscChargesEndACY	RebatePerUnit	RebateFixedAmount	RebatePercentage	RebatePerUnitBase	RebateFixedAmountBase	RebatePercentageBase	RebatePerUnitACY	RebateFixedAmountACY	RebatePercentageACY	ChargesValue	DateNo	CompanyID	StageTimestamp	LoadID	SalesPersonID	dim.Charges	dim.CommissionSalesGroup	dim.Company	dim.Currency	dim.Date	dim.DocumentSales	dim.DocumentSalesLine	dim.Employee	dim.FinancialDimension1	dim.FinancialDimension10	dim.FinancialDimension11	dim.FinancialDimension12	dim.FinancialDimension13	dim.FinancialDimension14	dim.FinancialDimension15	dim.FinancialDimension2	dim.FinancialDimension3	dim.FinancialDimension4	dim.FinancialDimension5	dim.FinancialDimension6	dim.FinancialDimension7	dim.FinancialDimension8	dim.FinancialDimension9	dim.GeneralType	dim.GlobalDocumentNo	dim.InventoryBatch	dim.InventoryDimensions	dim.InventorySerial	dim.InventorySize	dim.Item	dim.ItemDimension1	dim.ItemDimension2	dim.ItemDimension3	dim.ProcurementCategory	dim.ReasonCode	dim.ReturnReasonCode
2483	2483	1096100	2289928	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	31399	69210	0	0	0	20170402	0	0	0	0	2	3497625	1	0	5643938121	5639432555	3	2	2	17.2	0	0	0	11.998	0	0	0	17.2	0	0	0	11.998	0	0	0	17.2	0	0	0	11.998	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	42825	2	Binary.FromText(...)	7	300
3187	3187	1351286	2328934	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	5925	69210	0	0	0	20170411	0	0	0	0	2	3509963	1	0	5643979443	5639446841	3	50	50	975	0	0	0	687.401	0	0	0	975	0	0	0	687.401	0	0	0	975	0	0	0	687.401	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	42834	2	Binary.FromText(...)	7	371
3187	3187	1351286	2328935	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	19660	69210	0	0	0	20170411	0	0	0	0	2	3509963	1	0	5643979444	5639446841	3	5	5	525	0	0	0	418.523	0	0	0	525	0	0	0	418.523	0	0	0	525	0	0	0	418.523	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	42834	2	Binary.FromText(...)	7	371
3119	3119	1840954	2330530	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	2604	69210	0	0	0	20170411	0	0	0	0	2	3510531	1	0	5643980699	5639447335	3	8	8	136	0	0	0	91.394	0	0	0	136	0	0	0	91.394	0	0	0	136	0	0	0	91.394	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	42834	2	Binary.FromText(...)	7	371