Total not summing properly

[Anonymous]

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 )

[bcdobbs]

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]

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
)

[bcdobbs]

I think it has something to do with the flags for fiscal period.

What appears in the total when you change measure to...

SalesCohortPyfm =

VARStartDate=CALCULATE(MIN('Calendar'[calendar_date]),FiscalIndicators[is_pyfm_ind]=1)

VAREndDate=CALCULATE(MAX('Calendar'[calendar_date]),FiscalIndicators[is_pyfm_ind]=1)

Return

StartDate

[bcdobbs]

Actually can you share a picture of your model. Specifically how your calendar and FiscalIndicstors is related.

[Anonymous]

Sure its joined by calendar date.

[bcdobbs]

Can you talk me through 

is_pyfm_ind ... how many months have that as 1?

It looks to me like on the total row you're returning the full range of dates from your calendar table rather than just a months worth..

[Anonymous]

there is only 1 fiscal month and one fiscal year that has that 1 its fiscal year 2021 and fiscal month 2 I have exported the data in excel and verified it. The problem is the total the details are accurate.

[bcdobbs]

Sorry doing my best but not obvious so trying to rule out things you may already have checked.

Can you create a new measure and drop it into a card visual on a page with no filters.

Test Measure =

CALCULATE(MIN('Calendar'[calendar_date]),FiscalIndicators[is_pyfm_ind]=1)

[Anonymous]

I get it. Ive done all these calcs. That is the right date range. I dont get it. 🙂

[bcdobbs]

Ok next...

1) What relationship exists between calendar and Cohort?

2) If you go into the data table view and filter on ParentMinDate to the correct month can you see just the expected numbers. 

3) Are you able to run performance analyser in power bi and get the query generated by the table visual.

Think I might have to mock something up locally but am away from a laptop until Sunday evening (uk).

[Anonymous]

ParentMinDate-CALCULATE( min(Cohort[local_transaction_date]), ALLEXCEPT(cohort,Cohort[ParentPartnerID]))

Cohort is joined ot Partner table (customer) by Partner_sk=partner_sk
Cohort is joined to calendar by Calendar_date =  calendar_date

[Anonymous]

Also its important to note that I have another measure just like that one but its calculating the current year prior month and the total works great. Its using an indicator just like the other one.

[bcdobbs]

What happens to both measures if you set the relationship between calendar and cohort as inactive?

[Anonymous]

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