Comparing FY's In A Matrix From A Single Date Column Within Data Ideally Using SAMEPERIODLASTYEAR

[dougbert]

I've searched the forums for hours now, but can't seem to find a solution that will work for me.

I'm using a matrix that almost works, but not the way I want.  In the matrix below, FY19 is compared to FY20.  The problem is FY19 is displaying data for an entire fiscal year (FY), while FY20 is YTD.  That's clearly not a fair comparison since we have 2.5 months left in our FY.  I'd like to use SAMEPERIODLASTYEAR if possible, due to it's ability to filter right to the day when needed.

I have a 'FY Date' calendar marked as a Date Calendar I've customized with a definition for our FY.  FY is in the Columns field for the matrix.  The FY ($) is in Values.

FY is in the 'FY Date' calendar defined as: 

"FY", IF (Month([Date]) < 10, YEAR([Date]), YEAR([Date]) + 1)

I created 2 working measures:

Total Sales LFYTD = CALCULATE(CALCULATE([Total Sales], SAMEPERIODLASTYEAR('FY Date'[Date])), FILTER('FY Date', 'FY Date'[Date] <= TODAY()))

and...

Total Sales This FYTD = CALCULATE(SUM('Invoices Details'[INVOICE TOTAL]), FILTER('FY Date', 'FY Date'[Date] >= VALUE("01-OCT-19")))

Sorry for the hard-coded date.  I'll fix later.

...and Total Sales is a measure:

Total Sales = SUM('Invoices Details'[INVOICE TOTAL])

As a work-around, I created a bookmarked button that displayed the following 4 individual boxes, each as a measure (nevermind that the totals are different from the matrix values):

Not ideal...

BTW: I renamed [INVOICE TOTAL] as FY ($) in the matrix.

For some reason I can't figure out how to combine the 2 measures (LFYTD and FYTD) into a single measure which I need to keep the matrix functional as it is, as I need to drop a single measure into the Values field in order to display as above.

Any suggestions are appreciated.

[Ashish_Mathur]

Hi,

Try these measures:

Total Sales This FYTD = CALCULATE([Total Sales],DATESYTD('FY Date'[Date],"30/9")

Total Sales LFYTD = CALCULATE(Total Sales This FYTD,SAMEPERIODLASTYEAR('FY Date'[Date]))

Do these measures work?

[dougbert]

@Ashish_Mathur
Your 2nd measure displays (Blank) on a card.

As I said in my original message, I already have 2 measures that work correctly when displayed on separate cards.  However, I need to combine the 2 measures into a single measure.  When I add this new single measure into the Values field of the matrix visualization the matrix will handle the rest.

[Ashish_Mathur]

Hi,

I think there will have to be 2 measures.

[Ashish_Mathur]

Hi,

Share sample raw data and show the expected result.

[dougbert]

@Ashish_Mathur

Thanks for your reply. Here's some sample data:

CUSTOMER	INVOICE DATE	INVOICE TOTAL
A	1/31/2019	2836.49
A	7/25/2019	2562.85
A	11/8/2019	2730.3
A	12/11/2019	3013.05
A	2/18/2020	2568.24
B	1/31/2019	844
B	11/3/2019	912
B	12/31/2019	912
C	9/26/2019	2549.97
C	9/26/2019	2829.27
C	11/21/2019	2819.32
C	11/21/2019	2729.44
C	12/24/2019	3442.55
C	12/24/2019	2397.13
C	2/7/2020	2963.6
C	2/7/2020	2746
C	3/4/2020	2923.39
C	3/4/2020	3014.45
C	3/6/2020	2867.81

Here's what I need to see in the matrix from this sample data:

	FY19	FY20
TOTAL:	11622.58	36039.28
CUSTOMER	FY ($)	FY ($)
A	5399.34	8311.59
B	844	1824
C	5379.24	25903.69