Current Year vs. Prior Year based on Fiscal Year slicer

[Anonymous]

I'm sure I'm missing something simple.  The business wants to be able to run reports "as of" dates within the fiscal year and compare to the previous year.

I have created slicers based on our Fiscal Date Table as our fiscal year doesn't run on a calendar year or have same start day year over year.    The first slicer that requires an entry is Fiscal Year. The other slicers in order are:  Fiscal Month, Fiscal Week of the Year and Fiscal Day of the week.  Ideally they would not be able to enter Fiscal Year and then skip to fiscal day of the week, as I'm thinking processing speed would be crazy...and I'm not sure how to do this.  

I basically was just trying to get the Fiscal Year selection sorted before I started the drill down into the other selected data from the other slicers.  

I've created the following measures based on user selection:

- Current Year Value = SELECTEDVALUE('Fiscal Date table'[FiscalYear])

- Prior Year Value = MAX(Current Year Value) - 1

These measures appear to work as when I select 2018 - Current Year Value = 2018 and Prior Year Value = 2017 and so forth.  

Now here is where I get stuck.  I'd like to have 2 bar charts - 1 for current year and 1 for prior year.

1. Top 25 product sales by invoice qty where Invoiced Date = Current Year Value 

2. Top 25 product sales by invoiced qty where invoiced date = Prior Year Value

I tried the following 

Total Invoice Qty = SUM('Sales'[Invoice Qty]), YEAR('Sales'[Invoice Date]=[Current Year Value] 

I get the error "A function 'Calculate' has been used in a True/False expression that is used as a table filter expression.  This is not allowed."

Below is a sample data set for item id 45346. 

  

When I think about this in running a sql report I think "if fiscal year = x let current year = 'x' and prior year = x-1.

Again not sure how I'd get all the slicers into the mix.

[v-frfei-msft]

Hi @Anonymous ,

Try this one.

Total Invoice Qty =
CALCULATE (
    SUM ( 'Sales'[Invoice Qty] ),
    FILTER ( 'Sales', YEAR ( 'Sales'[Invoice Date] ) = [Current Year Value] )
)

[Anonymous]

Ok that almost works.  

Say I have a fiscal date table with following:

Fiscal Date Table Columns

This maps all the calendar dates from 2010 - 2025 to company fiscal dates. 

I've created another table: Year Table that only has one column and a listing of the years from 2010-2040. (Note:  I did this because when I selected FiscalYear from the Fiscal Date table for the year slicer, I was unable to do the prior year calculations as the table was already filtered even if I did the 'Edit Interactions' on the visualizations.

I created a copy of CalendarDate to change the date format to match MaxInvoiceDt from the Sales table.

Then I merged my Sales table with the Fiscal Date table via MaxInvoiceDate and CalendarDate - Copy.

When I try to create the following:

- Dynamic Year =

If('rpt SalesDataSet'[Fiscal Date Table.FiscalYear]=SELECTEDVALUE('Year Table'[Selected Year]),"Current", "Other") all I get returned is "Other" so I thought I'd use the Measure [Current Year] from above = 

Current Year Value = SELECTEDVALUE('Year Table'[Selected Year])

and do the following: 

If('rpt SalesDataSet'[Fiscal Date Table.FiscalYear]=SELECTEDVALUE('Year Table'[Selected Year]),"Current", "Other"). I still only get "Other" as the returned value.  

Ultimately then I could do the Total Invoice Qty = CALCULATE ( SUM ('rpt SalesDataSet'[Invoice Qty], FILTER ('rpt SalesDataSet, 'rpt SalesDataSet'[Dynamic Year] = "Current")

The issue with the the suggestion provided is that it doesn't tie the selected year to the fiscal year calendar, which was another initial error on my part.

Below is a screen shot of the Report I'm trying to build to provide some context.