@spaxia wrote:
Hello,
I have a little issue.
I have a Canvass Table to define some custom Periods, the Columns are:
CanvassID int
CanvassName String
FromDate Date
ToDate Date
FiscalYear String
I created a Measure Named Current FY
Current FY =
CALCULATE (
DISTINCT ( Canvass[FiscalYear] );
FILTER (
Canvass;
(
Canvass[FromDate]
<= ( DATE ( YEAR ( NOW () ); MONTH ( NOW () ); DAY ( NOW () ) ) )
&& (
DATE ( YEAR ( NOW () ); MONTH ( NOW () ); DAY ( NOW () ) )
<= ( Canvass[ToDate] )
)
)
)
)
If I place in the chart Area it correctly print FY 16/17 now I want to calculate the FY Actuals from a Table of Values
JC FY Actuals =
CALCULATE (
SUM ( 'Canvass Budget Actuals'[JCActuals] );
FILTER (
'Canvass Budget Actuals';
[CanvassID]
= CALCULATE (
DISTINCT ( Canvass[CanvassID] );
FILTER ( Canvass; Canvass[FiscalYear] = [Current FY] )
)
)
)
The above measure doesn't work as expected, I get the correct results only if i Change [Current FY] with the string "16/17".
Any help?
@spaxia
I'd doubt it is a filter context issue. What's going on if you put measure [Current FY] as a table column. To got the correct result, try
Current FY =
CALCULATE (
DISTINCT ( Canvass[FiscalYear] ),
FILTER (
ALL(Canvass),
(
Canvass[FromDate]
<=DATEVALUE(TODAY())
&& (
DATEVALUE(TODAY())
<= ( Canvass[ToDate] )
)
)
)
)
For more specific suggestion, please post some sample(in plain text or a uploaded file link) data and expected output.
