cancel
Showing results for
Did you mean:
Highlighted
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.

Regards,

Jamal Qamar

1 ACCEPTED SOLUTION

Accepted Solutions
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.

Regards,

Jamal Qamar

3 REPLIES 3
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

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

thank you

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.

Regards,

Jamal Qamar

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

Announcements

#### Challenge: Can You Solve These?

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

#### Community News & Announcements

Get your latest community news and announcements.

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

#### Win Power BI Swag with Community Kudopalooza!

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

Top Kudoed Authors
Users Online
Currently online: 111 members 1,531 guests
Recent signins: