Forum Discussion
Issue in Comparing Prior Period Sales
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
2 Replies
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 YuHi 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
