Hi,
I would like to calculate previous year's value, but I only get the correct number if I don't apply any slicer or filter.
The result I need is in the Total LY or in Total LY exact date column in below table
| Year | Total CY | Total LY | Total LY exact date |
| 2017 | 121 | | |
| 2018 | 219 | 121 | 121 |
| 2019 | 179 | 219 | 208 |
| Total (shown when the measure is displayed as single value such as in KPI Card) | 179 | 219 | 208 |
The scenario is that 2019 is still the current year of the data, thus the latest transaction date is not 31 Dec 2019, but before, let's say 27 Aug 2019.
Total LY shows the measurement at the end of the year, which is 31 Dec 2018 and 31 Dec 2017.
Total LY exact date shows the measurement at the same date of the each previous year, which is 27 Aug 2018 and 27 Aug 2017.
I managed to show the Total LY and Total LY exact date, if I use a single value visual, such as KPI card. And when I apply filter/slicer of a certain date, even though 2019, it showed blank value. There must be something I missed in the formula. Hope that I can get help on what is missed out and the correct formula.
To get the picture of what happened, here's the formula I used to get Total LY. The formula for Total LY exact date is similar, just a slight change on the filter date used. Appreciate if there's any input on this formula also
SUMX (
VALUES ( 'Contract'[Location] ),
VAR _maxDate =
CALCULATE (
MAXX ( 'Production', 'Production'[Production Date] ),
DATESYTD ( SAMEPERIODLASTYEAR ( 'Calendar Date'[Date] ) )
)
VAR _ProductionatLastYearDate =
CALCULATE (
SUM ( 'Contract'[Amount] ),
'Contract'[Date]
= _maxDate
)
RETURN
_ProductionatLastYearDate
)
