Previous Date Value Calculation is not working as expected - Immediate response required

[SelviPrabhu]

Hello There,

We have a Sales Table where the sales date values are stored twice in a month in the format of 2024-06-01, 2024-06-15 for each months.

Now I need to derive a measure to compare the " % of Current Sales vs Previous Sales " by using the logic below:

The logic= (CurrentSales – PreviosSales) / PreviousSales

Expected Output is in Percentage format keeping decimal place as 4.

Example: 0.0056% or 98.0000%

But the measure logic I'm using is not giving the expected result.

There is only Sales date provided as Filter. So If I Filter only by Sales Date, then its returning the result as 0.0000% but If I Add Product Id filter then I can see the expected result as 0.0056%. But as per the requirement only  Sales date filter should be used.

Could you pls. check the measure I provided and help me on deriving the expected output?

Example:

Sales Table:

ProductId	ProductName	ProductColor	SalesDate	SalesAmount
10001	TV	Black	2024-05-01	17.00
10001	TV	Black	2024-05-15	17.81
10001	TV	Black	2024-06-01	17.71
10001	TV	Black	2024-06-15	18.00

Filed used in Date Fitler: SalesDate

SelectedDateValue= 2024-06-01 

SalesAmount for Currently Selected date is = 17.71

Now Previous date for 2024-06-01  should be = 2024-05-15

 Sales amount for Previous Sales date (2024-05-15) is =17.81

The logic= (Sales – PreviosSales) / PreviousSales

%of CurrentSales vs Previous Sales = (17.71-17.81)/17.81 =0.0056 %

Expected Result:

ProductId	ProductName	ProductColor	SalesDate	SalesAmount	%of CurrentSales vs Previous Sales
10001	TV	Black	2024-06-01	17.71	0.0056 %

Measure Used:

%of CurrentSales vs Previous Sales = Var CurrentDate=MAX( Sales Table'[SalesDate])

Var PreviousDate = CALCULATE(MAX( Sales Table'[SalesDate]),Filter(all('Sales Table'), 'Sales Table'[SalesDAte] < CurrentDate))

Var PreviousSAles=  CALCULATE(sumx('Sales Table', 'Sales Table' [Sales Amount]), 'Sales Table''[SalesDAte]=PreviousDate,ALLSELECTED('Sales Table'))

Var Sales = 'Sales Table' [Sales Amount]),

Var Result =IF (PreviousSAles =0,0,ABS(Sales - PreviousSAles) / PreviousSAles)

Return  Result

@amitchandak @lbendlin @rajendraongole1  I'm tagging few I've seen in the helpers list. But whoever knows the solution for this, pls. help on deriving the solution.

[lbendlin]

This here is a forum where users help users, time permitting.  For urgent requests contact a Microsoft partner near you.

You can use a Quick Measure and then adjust it to your needs.

[SelviPrabhu]

Hi @lbendlin Could you pls. add sales date slicer to your testing report and check on this measure? Filter should be single select and choose the date as 2024-06-01. I tested using quick measure and the measure from above screenshots. But its not working out.

[lbendlin]

You will need to use REMOVEFITLERS or a disconnected calendar table for that.

[SelviPrabhu]

Hi @lbendlin Could you pls. confirm where exactly you are suggesting to use remove filters? in which part of the code?

[lbendlin]

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information. Do not include anything that is unrelated to the issue or question.

Need help uploading data? https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

[SelviPrabhu · Accepted solution]

Hi @lbendlin I can see the expected result now.  One of the field used along with this measure in the table was not aggregated like SUM (netamt). Don't Summarize was used for this column. So it was not returning the expected result. now my existing logic itself working as expected. Thanks for your inputs and suggestions on this @lbendlin 

%of CurrentSales vs Previous Sales = Var CurrentDate=MAX( 'Sales'[ SalesDate])

Var PreviousDate = CALCULATE(MAX( 'Sales'[ SalesDate]),Filter(all('Sales'), 'Sales'[ SalesDate] < CurrentDate))

Var PreviousSAles=  CALCULATE(sumx('Sales', Sales[ SalesAmount]), 'Sales'[ SalesDate]=PreviousDate)

Var CurrentSales = SUM(Sales[ SalesAmount])

Var Result =IF (PreviousSAles =0,0,ABS(CurrentSales - PreviousSAles) / PreviousSAles)

Return  IF(Result = 0, "0.00%", FORMAT(Result , "0.00000") & "%")