Last Month MTD till selected Date

[likhithar]

Hello All,

I'm showing Sales MTD and Last Month Sales MTD week wise trend,for Last Month Sales MTD i'm having full data for last month and in current month my data is till 16.There,for Last Month Sales MTD the value is showing till month end instead of current month last date.There is no interaction between day and wise wise trend graph.

In the below picture,When I put Day with week Sales LM MTD is 2,058 but the total  is 4,304 which is last full month sales instead of till 16.If I remove day and week as dimension ,week 3 Sales LM MTD is showing as 4,304 .It has to be 2,058.

DAX which I used is 

Sales MTD_1 = 

CALCULATE (

[Sales Amount],

FILTER(

     ALLSELECTED(dim_Date) ,

dim_Date[PeriodDate] <= MAX( dim_Date[PeriodDate] )

)

)/100000

Sales MTD = CALCULATE([Sales Amount],DATESMTD(dim_Date[PeriodDate]),ALL(dim_Date))/100000

Sales LM MTD = CALCULATE([Sales MTD],ALL(dim_date),DATEADD(dim_date[perioddate],-1,MONTH))

Sales LM MTD_1 = CALCULATE(CALCULATE([Sales Amount],DATEADD(dim_date[perioddate],-1,MONTH))/100000,FILTER(ALLSELECTED(dim_Date),dim_Date[PeriodDate]<=MAX(dim_Date[PeriodDate])))

[likhithar]

@Ashish_Mathur  Absolutely I worked Ashish..Thank you so much 🙂

[Ashish_Mathur]

You are welcome.

[Ashish_Mathur · Accepted solution]

Hi,

Does this measure help?

Sales LM MTD = CALCULATE([Sales MTD],datesbetween(dim_date[perioddate],edate(min(dim_date[perioddate]),-1),edate(max(dim_date[perioddate]),-1)))

This should work if the dim_date table goes only till today's date.