Forum Discussion

ThisIsHalloween's avatar
ThisIsHalloween
Helper I
2 years ago
Solved

Subtracting Two Measures Not Working

I have created two measures that calculate the YTD. Here is what the matrix looks like:

 

The data comes from two calculated tables that has the current fiscal year data in one table and last year's fiscal year data is in another table. Here is the code for the two measures: 

CY YTD = 

VAR CYDateEnd = 
MAX(CY[BI Date])

RETURN

IF(SUM(CY[Net Amount]) = 0, "",

    TOTALYTD(SUM(CY[Net Amount]),
    'Date'[Date],"6-30"))

 

PY YTD = 


VAR PYDateEnd = 
MAX(PY[BI Date])

VAR PYStartDate = 
min(PY[BI Date])

RETURN

CALCULATE(

    IF(TOTALYTD(SUM(PY[Net Amount]),
    DATESBETWEEN('Date'[Date],PYStartDate,PYDateEnd),'Date'[Date],"6-30")=0,"",TOTALYTD(SUM(PY[Net Amount]),
    DATESBETWEEN('Date'[Date],PYStartDate,PYDateEnd),'Date'[Date],"6-30")))

 

 

I created a third measure to subtract the two:

 

Difference YTD = 
CY[CY YTD] - PY[PY YTD]

 

 

No errors pop up when I made the measure. But when I try to add the column to the matrix (or place it in its own matrix), the visual breaks. 

 

 

If I drop the Measure into a tile, the correct overall total does populate: 

 

 

 

Any idea of why I can't put the Difference YTD measure in a Matrix by Month? 

 

Need Help
  • ThisIsHalloween's avatar
    ThisIsHalloween
    2 years ago

    I got it to work! Here is what I used for future reference:

     

    Difference YTD = 
VAR PYY =
TOTALYTD(SUM(PY[Net Amount]), SAMEPERIODLASTYEAR('Date'[Date]), "6-30")

VAR CYY = 
TOTALYTD(SUM(CY[Net Amount]),
    'Date'[Date],"6-30")


RETURN
CYY-PYY

3 Replies

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    2 years ago

    Hi,

    Share the download link of the PBI file.  I will solve it only with measures (not calculated table).  Hope you will be Ok with that.

  • ThisIsHalloween's avatar
    ThisIsHalloween
    Helper I
    2 years ago

    I got it to work! Here is what I used for future reference:

     

    Difference YTD = 
VAR PYY =
TOTALYTD(SUM(PY[Net Amount]), SAMEPERIODLASTYEAR('Date'[Date]), "6-30")

VAR CYY = 
TOTALYTD(SUM(CY[Net Amount]),
    'Date'[Date],"6-30")


RETURN
CYY-PYY
    • ThisIsHalloween's avatar
      ThisIsHalloween
      Helper I
      2 years ago

      Also for future reference to anyone who stumbles upon this, if you want the future months to show as blank, this worked for me: 

      Difference YTD = 
VAR PYY =
TOTALYTD(SUM(PY[Net Amount]), SAMEPERIODLASTYEAR('Date'[Date]), "6-30")

VAR CYY = 
TOTALYTD(SUM(CY[Net Amount]),
    'Date'[Date],"6-30")

VAR LAST = 
MAX(CY[BI Date])


RETURN
IF(SUM(CY[Net Amount])=0,"",CYY-PYY)