So for this example it is best explained with an image below, then some additional context afterwards

  • If you look above we have got the [Sum of Sales] for Dec 2010-11
    • NOTE: This I selected in the Slicer on the right hand side.
  • Then I have created a Year to Date (YTD) measure which is the running total for the Year
    • NOTE: This is the measure below.

      YTD-MTD-CY = TOTALYTD([Sum of Sales],’Date'[Calendar Date],”06/30″)

  • As you can see for Dec 2010-11 with the measure [YTD-MTD-CY] I have the Year to date value.

Now the requirement was if the user selected any Fiscal Month (EG: Feb) from the Slicer, they are looking for the Feb 2009-10 value for the Previous Year (Feb 2009-10). So once again if I show this in a picture this is the value that I am looking to get.

DAX Measures

Below are the DAX measures that I used with an explanation afterwards.

  • DAX Measure
    YTD-MTD-CY =
TOTALYTD ( [Sum of Sales], 'Date'[Calendar Date], "06/30" )

    • What this measure is doing, is getting the YTD total for the current Fiscal Year for the [Sum of Total Liability.]
  • DAX Measure
    ZCALC - YTD-MTD-PY =
CALCULATE (
  [YTD-MTD-CY],
  PREVIOUSYEAR ( 'Date'[Calendar Date], "06/30" ),
 'Date'[Fiscal Month] = VALUES ( 'Date'[Fiscal Month] )
)

    • This is the DAX measure that does all the work required to get the result that I wanted.
    • The first thing is that I used the previous measure [YTD-MTD-CY]
    • Next in the filters, the first filter that I used was to go back to the PREVIOUSYEAR highlighted in BLUE
      • Again with the PREVIOUSYEAR I am ensuring that it goes back 1 year, based on the Fiscal End Month.
      • NOTE: So in the current context it will now be going back the entire Previous Year, and not stopping at the month selected.
    • The final filter that I added is where I am filtering the Previous Years values, to stop at the value selected based on what was selected on the slicer, highlighted in ORANGE.
      • So when a value is selected on the Slicer it will return this value to the filter.
      • And then instead of the values going to the end of that current fiscal year, it will stop at the Fiscal Month Selected.
  • DAX Measure
    ZCALC - YTD-MTD-PY Blank =
CALCULATE (
  [YTD-MTD-CY],
  PREVIOUSYEAR ( 'Date'[Calendar Date], "06/30" ),
  'Date'[Fiscal Month] = "Jun"
)

    • This measure that I created above, is so that if nothing is selected on the Fiscal Month Slicer, then return the last month of the Fiscal Year which is Jun.
    • The measure is identical to the one above, but I just hardcoded the Fiscal Month Value.
  • DAX Measure
    YTD-MTD-PY =
IF (
  HASONEVALUE ( 'Date'[Fiscal Month] ),
  [ZCALC - YTD-MTD-PY],
  [ZCALC - YTD-MTD-PY Blank]
)

    • This is the final measure, where I evaluate if a value is selected on the Slicer (TRUE), then use the measure [ZCALC – YTD-MTD-PY]
    • If nothing is selected (FALSE) then use the measure [ZCALC – YTD-MTD-PY-BLANK]

Results

So now I have the measure that I need with the correct results showing below.

  • As you can see above Aug was selected in the Fiscal Month Slicer.
  • So when you view the top table, you can see that the YTD value is $491,100,640 for Aug 2010-11
  • Then when you look at the bottom table you can see that for the measure
    [YTD-MTD-CY] is shown correctly for 2010-11
  • And then when you look at the bottom table for the measure
    [YTD-MTD-PY], this is showing the Previous Years
    value on the row for 2011-12

Conclusion

So as you can see above, when you change the value in the Fiscal Month Slicer, it will then correctly update the measure to show up to and including the Fiscal Month
selected for the Previous Year.

And here is what it looked like when completed when I selected Nov

And then once again when I selected Mar