Forum Discussion
Comparing FY's In A Matrix From A Single Date Column Within Data Ideally Using SAMEPERIODLASTYEAR
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.
5 Replies
- Ashish_MathurSuper User
Hi,
Share sample raw data and show the expected result.
- AnonymousNot applicable
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
- Ashish_MathurSuper User
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?
- AnonymousNot applicable
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_MathurSuper User
Hi,
I think there will have to be 2 measures.