Forum Discussion
Helper VLY MTD Calculation
i want to calculate LYMTD
Used Measure
if i select any item IN FILTER
LYMTD showing blank
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 TeamIf 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
6 Replies
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 TeamIf 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
- bhanu_gautam
Super User
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_oNOT 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
- Greg_Deckler
Community Champion
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 - Kedar_Pande
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)
)
) 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
