help on filtering

[Anonymous]

hi all,

need some advice here on the chart filtering.

i have 3 measure calculated using the filtering

next i need to use the above measure to do further % change calculation.

however i got blank, i think it got to do with filtering but doesnt get the correct value.

Can anyone advice? Thank you.

https://gofile.io/?c=xEmO0k

[v-eachen-msft]

Hi @Anonymous ,

You can try this measure. 

Last 12 Mths New =
VAR _Curr_start =
    MAXX ( DATA1, DATEADD ( STARTOFMONTH ( DATA1[Date1] ), -11, MONTH ) )
VAR _Curr_END =
    MAX ( DATA1[Date1] )
RETURN
    SUMX (
        FILTER (
            ALL ( DATA1 ),
            DATA1[Date1] >= _Curr_start
                && DATA1[Date1] <= _Curr_END
                && DATA1[Product] = SELECTEDVALUE ( DATA1[Product] )
        ),
        DATA1[Value]
    )

Here is the result.

[Anonymous]

@v-eachen-msft 

Thanks for the formula.

[amitchandak · Accepted solution]

Please check the file.

https://www.dropbox.com/s/3svdbxr8akcfctc/filter.pbix?dl=0

Issues are with the data type.

[amitchandak]

The last 12-months table cal has issues.

You were able to see data because you have stopped time filter on that visual.

Please see if this calculation suffices your needs.

Last 12 Mths New = 
VAR _Curr_start = Maxx(dates,DATEADD(STARTOFMONTH(Dates[Date]),-11,MONTH))
VAR _Curr_END =   Max(Dates[Date])
Return
CALCULATE(sum(DATA1[Value]),ALL(DATA1),DATA1[Date]>=_Curr_start && DATA1[Date]<=_Curr_END)

https://www.dropbox.com/s/3svdbxr8akcfctc/filter.pbix?dl=0

[Anonymous]

@amitchandak but the values are wrong are the new last 12 mths?

[amitchandak]

Calc of 12 month is correct. Currently, the calendar end date is 31-Dec-2019.  So max of that has been taken. Now there two options.

1. when you have a slicer, it will respond to that. I checked it

2. Or in the max, you can use the max of sales date.

[Anonymous]

@amitchandak hi,the value of last12 mths is based on all products?

i tried using adding product filter but the salechange value doesnt change and the last12 mths sale value wrong.

[amitchandak]

Remove all(DATA1) from the formula. I added that when I did not realize that edit interaction casing issue.

Last 12 Mths New = 
VAR _Curr_start = Maxx(dates,DATEADD(STARTOFMONTH(Dates[Date]),-11,MONTH))
VAR _Curr_END =   Max(Dates[Date])
Return
CALCULATE(sum(DATA1[Value]),DATA1[Date]>=_Curr_start && DATA1[Date]<=_Curr_END)

[Anonymous]

@amitchandak 
I not sure where i gone wrong, but i cant get the value for last12mth correctly.

I am trying to get product A, Jan to Dec 2018 sale total = 824
Can you advice? Thanks so much.

https://gofile.io/?c=gGH9t0

[amitchandak]

Please check the column you have taken "date" from the Data1 filter. I think the date of DATA1 is not date. Check you table in model view.

Date1 is in date format from Data1. Also, the relation between Data1 and dates is one-directional. So you have to date from the dates table.

That is giving the correct answer.