Forum Discussion
Helper Iinconsistent Measure results by date
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?
6 Replies
cphiteBillings 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.
