Custom Fiscal Year Calendar Issue

---

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.