cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
mmacy5321 Frequent Visitor
Frequent Visitor

Current Year vs. Prior Year based on Fiscal Year slicer

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. 

 

  Data Set.PNG

 

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.

2 REPLIES 2
Community Support Team
Community Support Team

Re: Current Year vs. Prior Year based on Fiscal Year slicer

Hi @mmacy5321 ,

 

Try this one.

 

Total Invoice Qty =
CALCULATE (
    SUM ( 'Sales'[Invoice Qty] ),
    FILTER ( 'Sales', YEAR ( 'Sales'[Invoice Date] ) = [Current Year Value] )
)
Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
mmacy5321 Frequent Visitor
Frequent Visitor

Re: Current Year vs. Prior Year based on Fiscal Year slicer

Ok that almost works.  

 

Say I have a fiscal date table with following:

Fiscal Date Table.PNGFiscal 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.  
As of Dates Screen Capture.PNG

Helpful resources

Announcements
Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Power BI Helps Homeless and Trouble Youth

Power BI Helps Homeless and Trouble Youth

We spoke with Power BI Super User, Greg Deckler, about his charity work

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 327 members 3,033 guests
Please welcome our newest community members: