Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

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

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 ...
Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    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") & "%")

     
lbendlin's avatar
lbendlin
Icon for Super User rankSuper User
2 years ago

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

Anonymous's avatar
Anonymous
Not applicable
2 years ago

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

  • lbendlin's avatar
    lbendlin
    Icon for Super User rankSuper User
    2 years ago

    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-Forum/ba-p/963216

    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/1447523

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      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") & "%")