Forum Discussion
MTD calculation
Hi rajasekar_o
Try this:
LYMTD Sales = VAR _year = SELECTEDVALUE('Calendar'[Year]) VAR _month = VALUES('Calendar'[Month]) RETURN IF( ISFILTERED('Calendar'[Year]) && ISFILTERED('Calendar'[Month]), CALCULATE( SUM('Sales'[Netamount]), FILTER( 'Sales', YEAR('Sales'[invdate]) = _year - 1 && MONTH('Sales'[invdate]) IN _month ) ), TOTALMTD( SUM('Sales'[Netamount]), 'Sales'[invdate] ) )Regards,
Nono Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi rajasekar_o
Thank you very much 123abc for your prompt reply.
For your question, here is the method I provided:
"Calendar"
Calendar =
ADDCOLUMNS(
CALENDAR(DATE(2024, 1, 1), DATE(2024, 12, 31)),
"Year", YEAR([Date]),
"Month", MONTH([Date]),
"Day", DAY([Date])
)
"Sales"
create a measure.
MTD Sales =
VAR _year = VALUES('Calendar'[Year])
VAR _month = VALUES('Calendar'[Month])
RETURN
IF(
ISFILTERED('Calendar'[Year]) && ISFILTERED('Calendar'[Month]),
CALCULATE(
SUM('Sales'[Netamount]),
FILTER(
'Sales',
YEAR('Sales'[invdate]) in _year
&&
MONTH('Sales'[invdate]) IN _month
)
),
TOTALMTD(
SUM('Sales'[Netamount]),
'Sales'[invdate]
)
)
Here is the result.
No slicer
Selective slicer
Regards,
Nono Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
thank you its working
simmularly how to calculate lastyear mtd
if i select sep month LYMTD need to calculate value
1-9-2023 to 27-9-2023
if i select jan or feb month LYMTD need to calculate value
then need to show full month value
if i select multiple month then show selected month sales from lastyear