Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

help on filtering

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

 

 

1 ACCEPTED SOLUTION

10 REPLIES 10
v-eachen-msft
Community Support
Community Support

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.

2-1.PNG

 

Community Support Team _ Eads
If this post helps, then please consider Accept it as the solution to help the other members find it.
Anonymous
Not applicable

@v-eachen-msft 

Thanks for the formula.

 

Please check the file.

 

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

 

Issues are with the data type.

amitchandak
Super User
Super User

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
Not applicable

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

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.

 

Screenshot 2019-09-14 18.20.36.png

Anonymous
Not applicable

@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.

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
Not applicable

@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

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.