Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Total not summing properly

Hello. I have created a measure that calculates the sales for new customers for fiscal prior year last full month, Fiscal February fiscal year 2021. The ParentMinDate is the date the customer had their first order. I have created some indicators in our calendar that pulls the proper date ranges for this. The date range is accurate for the pyfm_indicator. I have tested it. The $433? is accurate. Why am I getting the $3M as a total. I’ve tried everything I can think of. any help would be appreciated.

SalesCohortPyfm =
VAR StartDate=CALCULATE(MIN('Calendar'[calendar_date]),FiscalIndicators[is_pyfm_ind]=1)
VAR EndDate=CALCULATE(MAX('Calendar'[calendar_date]),FiscalIndicators[is_pyfm_ind]=1)

Return
CALCULATE(
[InvoicedSalesCohort],
Cohort[ParentMinDate] >=StartDate
&& Cohort[ParentMinDate] <=EndDate )

 

Need Help

14 Replies

  • bcdobbs's avatar
    bcdobbs
    Community Champion
    4 years ago

    A few thoughts...

    1) Can you share  the DAX for [InvoicedSalesCohort].

    2) If you comment out your current return and instead replace with RETURN StartDate what is shown in the total? (Then do same with RETURN EndDate.

     

    Is it possible to share a sanitised PBIX file? 

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Hi Ben, yes I have tried to replace the VAR with actual dates and Iget the same total. The calc for  [InvoicedSalesCohort] is jsut the sum of a column. I get the same total when I put in the sum(Cohort[Sales]) instead of [InvoicedSalesCohort].

       

      InvoicedSalesCohort:=
      CALCULATE(
            sum(Cohort[Sales]),
                Cohort[is_revenue_ind]=1
      )

      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago

        Im not able to send a workbook its connected to a network.