Displayed selected year and prior year on slicer

[jwin2424]

Hello. 

I am trying to get it so my clustered bar chart will show both the selected year on the slicer AND the prior year. I found a formula and a how-to online (Here), but it doesn't seem to be working. 

On my chart, you can see I have the slicer selected for 2021, but it is not showing 2021 and 2020 on the graph. 

1. I created a calculated table using this formula:

2. I then created a relationship between this table and my Dim Calendar table. 

3. I used the newly created table year for my slicer (replacing the Dim Calendar year)

4. I created a new measure

5. I filtered the slicer with the new measure 

My calendar range is from 1995-2030 if that makes a difference.

Thanks!

[samdthompson · Accepted solution]

Hello, the formula above will work with a wee change to from week to month, in this case:

Measure 2:

Net US Revenue PY = 

VAR _CurrentPeriod = SELECTEDVALUE( Dates[Fiscal Month] )

VAR _CurrentYear = SELECTEDVALUE( Dates[Fiscal Year] )

VAR _Result =

SUMX(

FILTER( ALL( Dates ),

Dates[Fiscal Month] = _CurrentPeriod &&

Dates[Fiscal Year] = _CurrentYear - 1 ),

[Net US Revenue] )

RETURN

_Result

[jwin2424]

Sorry one more question! I know with selectedvalues it does not show a total. Is there a workaround to see the grand total?

[samdthompson]

Hello, something like:

Net US Revenue PY = 

VAR _CurrentPeriod = SELECTEDVALUE( Dates[Fiscal Month] )

VAR _CurrentYear = SELECTEDVALUE( Dates[Fiscal Year] )

VAR _Result =

SUMX(

FILTER( ALL( Dates ),

Dates[Fiscal Month] = _CurrentPeriod &&

Dates[Fiscal Year] = _CurrentYear - 1 ),

[Net US Revenue] )

RETURN

IF( HASONEVALUE(Dates[Fiscal Month]) , _Result ,

SUMX(FILTER( ALL( Dates ),Dates[Fiscal Month] = _CurrentPeriod &&Dates[Fiscal Year] = _CurrentYear - 1 ),[Net US Revenue] ) , _Result)

[jwin2424]

Came back with "IF function has too many arguements. It can only support 3." It's not a huge deal. I am turning this into a clustered column chart instead. I really just wanted the ability to do current yr vs prior yr which you solved for me. I will find another means to show YoY grand total and go a different route. I appreciate the help!

[jwin2424]

Not quite: 

[samdthompson]

Huh, Weird, try this:

VAR _CurrentPeriod = SELECTEDVALUE( Dates[Fiscal Month] )

VAR _CurrentYear = SELECTEDVALUE( Dates[Fiscal Year] )

VAR _Result =

SUMX(

FILTER( ALL( Dates ),

Dates[Fiscal Month] = _CurrentPeriod &&

Dates[Fiscal Year] = _CurrentYear - 1 ),

[Net US Revenue] )

RETURN

IF( HASONEVALUE(Dates[Fiscal Month]) , _Result ,

SUMX(FILTER( ALL( Dates ),Dates[Fiscal Month] = _CurrentPeriod &&Dates[Fiscal Year] = _CurrentYear - 1 ),[Net US Revenue] ) , _Result)

[jwin2424]

I must have mistyped something when doing this, but I entered it in again and it works exactly as needed. Thank you!

[samdthompson]

Hello, make the sure you are using a date hierarchy on the x axis (Yr, Mth, Day). This solution will work unless you are using a 544, 445 or 454 wk pattern. If you are doing that there needs to be a little finessing to the formulas. There are a bunch of ways to go about this but my go to is Ken Puls's one which goes back to a fit for purpose calendar: Creating a custom calendar in Power QueryThe Excelguru Blog. 

The 2nd measure  you wrtoe for the PY is then changed (you will need to fix the column names and table name to what ever the new date table has):

Measure 2:

Net US Revenue PY = 

VAR _CurrentPeriod = SELECTEDVALUE( Dates[WeekOfYear] )

VAR _CurrentYear = SELECTEDVALUE( Dates[Year] )

VAR _Result =

SUMX(

FILTER( ALL( Dates ),

Dates[WeekOfYear] = _CurrentPeriod &&

Dates[Year] = _CurrentYear - 1 ),

[Net US Revenue] )

RETURN

_Result

Id really like to see MS make 445, calendars an easier thing to grapple with since they are currently much harder than they really should be.

[jwin2424]

It seems like you are on the right path, but I decided against the slicer because I want the slicer to relate to all my tables, but I only want one table to show prior year. I am trying to get a formula in the table instead of the slicer now. Something like what @samdthompson mentioned. 

When I used sam's formula, it did exactly what I wanted EXCEPT it returned the entire month (Jan 1st to Jan 31st) instead of returning to me the fiscal period (Jan 1st to Jan 28th). It doesn't appear that time intellegence formulas will work because it can only refer to DATE formats. 

Can I do something similiar to the above where I grab the sum from prior period?

This is my goal. Something that says give me the sum from the same fiscal month from the fiscal year - 1? insteaf of referencing the DATE column?

NUMBER FORMAT	NUMBER FORMAT
Fiscal Year	Fiscal Month	Total Revenue	Total PY Revenue
2020	1	$1,524,856.00
2020	2	$652,158.00
2020	3	$621,835.00
2020	4	$1,548,265.00
2020	5	$1,234,658.00
2020	6	$987,652.00
2020	7	$921,458.00
2020	8	$1,206,589.00
2020	9	$985,448.00
2020	10	$998,635.00
2020	11	$1,689,547.00
2020	12	$1,489,756.00
2021	1	$968,523.00	$1,524,856.00
2021	2	$879,546.00	$652,158.00
2021	3	$895,741.00	$621,835.00
2021	4	$1,659,876.00	$1,548,265.00
2021	5	$1,789,548.00	$1,234,658.00
2021	6	$1,235,684.00	$987,652.00
2021	7	$897,546.00	$921,458.00
2021	8	$789,485.00	$1,206,589.00
2021	9	$986,341.00	$985,448.00
2021	10	$1,635,874.00	$998,635.00
2021	11	$1,659,874.00	$1,689,547.00
2021	12	$987,685.00	$1,489,756.00

[samdthompson]

Hello, why not use the SAMEPERIODLASTYEAR()? So something like:

Measure 1:

Net US Revenue = SUM(Sales[Net US Revenue])

Measure 2:

Net US Revenue PY = CALCULATE([Net US Revenue], SAMEPERIODLASTYEAR('Dim Tek Calendar'[Dates]))

That way you select say 2021 and the 2021 measure for [Net US Revenue] will show up along with the [Net US Revenue PY] as a separate bunch of columns for the 2021 months

[jwin2424]

Ok. This actually did work EXCEPT there is one issues. The sample period is summurizing the data based on the DATE, and the sales are based on FISCAL dates. When I sum the totals, it sums the sales based on the fiscal date (which is what I want), but when I do the sample period, it sums the sales based on the calendar date. I had to create a table for the fiscal date month, quarter, etc. and the format is a number (not a date), so i can't reference it in the measure without receiving an error (because it is not a date). Is there another way?

[jwin2424]

I don't quite understand the instructions. I replaced the Net USD with Measure 1 in the "Value" area of the chart. Where does Measure 2 go? I can't put it on the clustered chart with Measure 1. 

Thanks!