Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

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

    • Anonymous's avatar
      Anonymous
      Not applicable

       

      CUSTOMERINVOICE DATEINVOICE TOTAL
      A1/31/20192836.49
      A7/25/20192562.85
      A11/8/20192730.3
      A12/11/20193013.05
      A2/18/20202568.24
      B1/31/2019844
      B11/3/2019912
      B12/31/2019912
      C9/26/20192549.97
      C9/26/20192829.27
      C11/21/20192819.32
      C11/21/20192729.44
      C12/24/20193442.55
      C12/24/20192397.13
      C2/7/20202963.6
      C2/7/20202746
      C3/4/20202923.39
      C3/4/20203014.45
      C3/6/20202867.81

       

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

       FY19FY20
      TOTAL:11622.5836039.28
      CUSTOMERFY ($)FY ($)
      A5399.348311.59
      B8441824
      C5379.2425903.69
  • 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?

     

    • Anonymous's avatar
      Anonymous
      Not 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.