Reply
Regular Visitor
Posts: 18
Registered: ‎10-05-2016
Accepted Solution

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

 

 

 


Accepted Solutions
Regular Visitor
Posts: 18
Registered: ‎10-05-2016

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


All Replies
Regular Visitor
Posts: 27
Registered: ‎03-20-2017

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

Regular Visitor
Posts: 18
Registered: ‎10-05-2016

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

Regular Visitor
Posts: 27
Registered: ‎03-20-2017

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