LY MTD Calculation

[rajasekar_o]

Hi team,
i want to calculate LYMTD

Used Measure

LY MTD Sales =

VAR CurrentDate = MAX('date'[Date])

VAR PreviousYearDate = EDATE(CurrentDate, -12)

RETURN

CALCULATE(

    [sales amount],

    FILTER(

        ALL('date'),

        'date'[Date] <= CurrentDate &&                  

        'date'[Date] <= PreviousYearDate &&            

        'date'[Date] >= DATE(YEAR(CurrentDate)-1, MONTH(CurrentDate), 1)

    )

)

if i select any item IN FILTER 
LYMTD  showing blank

[v-yaningy-msft · Accepted solution]

Hi, @rajasekar_o 

Thanks for Kedar_Pande, Greg_Deckler and bhanu_gautam replies. Are you currently addressing this issue? If not, you can refer to the following dax.

LY MTD Sales = 
VAR _currentDate = MAX('Table 2'[Inv Date])
VAR _previousYearDate = EDATE(_currentDate, -12)
VAR _item = SELECTEDVALUE('Table 2'[Item])
RETURN
CALCULATE(
    SUM('Table 2'[Amount]),
    FILTER(
        ALL('Table 2'),
        'Table 2'[Inv Date] <= _currentDate &&                  
        'Table 2'[Inv Date] >= _previousYearDate &&
        'Table 2'[Item] = _item
    )
)

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

[v-yaningy-msft]

Hi, @rajasekar_o 

Thanks for Kedar_Pande Greg_Deckler and bhanu_gautam reply. They all give suggestions for modifying dax, you can try their methods and if they don't meet your needs, you can describe them in detail. Or just provide the pbix file without sensitive data for testing.

Best Regards,
Yang
Community Support Team

[Kedar_Pande]

@rajasekar_o ,

Corrected Measure:

LY MTD Sales =
VAR CurrentDate = MAX('date'[Date])
VAR PreviousYearStart = DATE(YEAR(CurrentDate)-1, MONTH(CurrentDate), 1)
VAR PreviousYearEnd = EOMONTH(PreviousYearStart, 0)
RETURN
CALCULATE(
[sales amount],
FILTER(
ALL('date'),
'date'[Date] >= PreviousYearStart &&
'date'[Date] <= PreviousYearEnd &&
'date'[Date] <= EDATE(CurrentDate, -12)
)
)

[Greg_Deckler]

@rajasekar_o Maybe:

LY MTD Sales =
  VAR CurrentDate = MAX('date'[Date])
  VAR PreviousYearDate = EDATE(CurrentDate, -12)
  VAR __Table = FILTER( ALL( 'Sales' ), 'Sales'[Date] <= PreviousYearDate && 'Sales'[Date] >= DATE( YEAR( CurrentDate) - 1, MONTH( CurrentDate ), 1 ) )
  VAR __Result = SUMX( __Table, [Amount] )
RETURN
  __Result

[bhanu_gautam]

@rajasekar_o , Try using below DAX

LY MTD Sales =
VAR CurrentDate = MAX('date'[Date])
VAR PreviousYearDate = EDATE(CurrentDate, -12)
RETURN
CALCULATE(
[sales amount],
FILTER(
ALL('date'),
'date'[Date] <= CurrentDate &&
'date'[Date] >= DATE(YEAR(CurrentDate)-1, MONTH(CurrentDate), 1) &&
'date'[Date] <= DATE(YEAR(CurrentDate)-1, MONTH(CurrentDate), DAY(CurrentDate))
)
)

[rajasekar_o]

NOT wroking

Inv Date	Item	Qty	Amount
01-01-2023	ITEM A	5	670
03-01-2023	ITEM A	6	804
03-01-2023	ITEM B	4	536
06-09-2023	ITEM A	3	402
07-09-2023	ITEM B	7	938
01-10-2023	ITEM B	4	536
06-10-2023	ITEM A	8	1072
06-10-2023	ITEM B	4	536
30-10-2023	ITEM A	8	1072
05-09-2024	ITEM B	3	402
09-09-2024	ITEM A	7	938
01-10-2024	ITEM B	5	670
02-10-2024	ITEM A	8	1072
04-10-2024	ITEM B	3	402