Sales trend and latest month detailed required based on slicer range

[jamalq123]

Hi,

I have a table which includes fields "Date", "Product Name" and "Sales Amount" on a monthly basis for the CY 2015 and 2016.

I have a monthly slicer as well as yearly slicer.

I made a Column Chart showing monthly sales trend on an overall basis, this is workig well when we change the slicer range. 

But, simultaneously I am also interested in to make a table which should show all the field i.e. Product name, sales amount and sales date based on the latest last month of slicer.

I do not want to use Edit Interactions, since when I will use interactions then i will have to bliock some visuals.

I want that when i change the slicer range, trend chart also move accordingly as well as give me the detailed report of latest last month of the slicer range.  

I have taken the following steps but its not working:

1) I created a measure name "Sum Sales" and the formula is given below: I want to make it automate. In the given below formula I have taken a hard coated year i.e. 2015 and month no i.e. 2. (Output is good as per my requirement but its not dynamics since here i have 2 hard coded variabls i.e. 2015 and month numnber 2. I also tried to use Max function for the year and month number but could not achieve the desired result.

Sum Sales = CALCULATE(SUMX(Sales,Sales[Sales]),FILTER(Sales,Sales[Date].[Year]=2015),FILTER(Sales,Sales[Date].[MonthNo]=2))

However, I tried another formula but the out put is wrong.

Sum Sales1 = CALCULATE(SUMX(Sales,Sales[Sales]),FILTER(Sales,Sales[Date].[Date]=MAX(Sales[Date])))

Kindly resolve the issue. See below the Snap.

 https://app.powerbi.com/view?r=eyJrIjoiZDc5ZDE1YmUtNDhlMC00MDE0LWEzYjctZDJlZDkzM2YyZjgwIiwidCI6ImY5Y...

Regards,

Jamal Qamar

[edavila]

Jamal

I have almost the same problem do you mind sharing the tables (pictures) to see if we have the same in my part Im measuring the sales in 2 separate tables but want to have a comparison as well and a variance and Im struggling here as I not to good in the formulas but im trying to ask questions and look for examples and yurs looks like is very closed to mine

by the way can you tell me how did you make those max of sales, months ETC

thank you

[jamalq123 · Accepted solution]

Hi Edavila,

I resolved the issue. 

I made one big mistake that i have not a single table of Date which includes date, month, year and endofmonth. and made a relation ship withthe main table which have date wise transaction. I calculated a measure which resolved my case. I have highlighetd the core part of the formulae. 

GRPSF = CALCULATE(SUM(Data1[GRPs]),FILTER(ALL(dDate1),dDate1[EoMonth]=MAX(dDate1[EoMonth])))

After using the formulae, now i am in a position to see the trend of sales as well as last month detailed report. 

I can share the PBIX file, please send me your email address.

Regards,

Jamal Qamar

[edavila]

Thank you and I working also in making a sales report with graphs and everything this is my email edavila@vilore.com

and please xhare and I can share mine for your review and support

thanks