cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
jamalq123 Regular Visitor
Regular Visitor

Sales trend and latest month detailed required based on slicer range

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.

 

Sales.png

 

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

 

 

 

Regards,

 

Jamal Qamar

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
jamalq123 Regular Visitor
Regular Visitor

Re: Sales trend and latest month detailed required based on slicer range

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

View solution in original post

3 REPLIES 3
edavila Regular Visitor
Regular Visitor

Re: Sales trend and latest month detailed required based on slicer range

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

GL Accounts-1.PNGGL Accounts-2.PNGGL Accounts-3.PNG

 

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

 

thank you

jamalq123 Regular Visitor
Regular Visitor

Re: Sales trend and latest month detailed required based on slicer range

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

View solution in original post

edavila Regular Visitor
Regular Visitor

Re: Sales trend and latest month detailed required based on slicer range

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

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 111 members 1,531 guests
Please welcome our newest community members: