inconsistent Measure results by date

[cphite]

So I have a table BILLING_DATA which is a list of invoices, which is joined to a table FISCAL_CALENDAR by the invoice date.

The FISCAL_CALENDAR table has a column (added in power bi) called isCY which is defined as:

isCY = if('Fiscal Calendar'[Year]=year(today()), "Y", "N")

And then I have a Measure on my BILLING_DATA table, to show billings that occurred during the current year, defined as:

Billings CY = 
CALCULATE(
    'Billings Details'[Billings],
    FILTER(
        'Fiscal Calendar',
        'Fiscal Calendar'[isCY] = "Y"))

So basically, if the invoice occurred during the current year, it should have a value.  However, what I see instead is below...  it's picking it up for some dates but not others.  I know that the join between BILLING_DATA and FISCAL_CALENDAR is good because it's picking up the last three columns from the second table, and the isCY column is "Y" - so why isn't it rolling up those dates?

[tamerj1]

@cphite 

What is the code of [Billings]?

what happens if you remove all the columns from the date table?

what happens if you also remove [Billings]?

[cphite]

Billings is:

Billings = VAR  Billings_USD = 
CALCULATE(SUM('Billings Details'[Billings USD]))

VAR  Billings_Func=
CALCULATE(SUM('Billings Details'[x-Billings Accounted]))

VAR Currency_Type =
SELECTEDVALUE ('Currency'[Currency],"USD")

RETURN
    SWITCH(
        TRUE (),
        Currency_Type = "USD", Billings_USD,
        Currency_Type <> "USD", Billings_Func,
        Billings_USD
    )

The columns Billing USD and x-billings Accounted are identical - they both pull from the same column in the data source.  They only exist in case we want to account for other currencies. 

I see no change if I remove the Billings measure, or the calendar columns.  Currency[Currency] is always USD as it is the only value in the table.

[tamerj1]

@cphite 

Ok would you please sort the visual by InvNo and see if you have duplicates?

[cphite]

There are no duplicates...  just double checked in the report, and the source is grouping by the invoice number.

[tamerj1 · Accepted solution]

@cphite 

Please try

Billings CY =
CALCULATE (
'Billings Details'[Billings],
FILTER ( 'Fiscal Calendar', 'Fiscal Calendar'[isCY] = "Y" ),
ALLEXCEPT ( 'Billings Details', 'Billings Details'[InvNo] )
)

I suspect that you might have have two directional relationship?

[cphite]

Close!  I had another table for orders that was also joined to FISCAL_CALENDAR and it was set to filter both ways.  I changed that to single and now the measure is working 😄

Thanks!  I wouldn't have found it without your suggestion!  Still kinda new at this Power BI thing...