cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Gordon2 Member
Member

Dynamic filter on MONTH

I am looking for a measure to compute Sales in a specyfic scenario.
When you look closer at my data set (call it Table1) you may notice 2018 is not closed yet.
The latest MONTH for 2018 is 4 whilst for 2017 I have full data for all 12 months.
I would like to compare 2018 Sales with analogical period of 2017
I mean, sales for 1-4 months of 2018 with sales for 1-4 months of 2017.
A trick is my Table1 is not fixed and will grow when new data for future months of 2018 will come.

Here is the data set:

 

Table1

YEARMONTHSALES
20171100
20172222
20173102
20174123
20175322
20176105
20177106
20178323
20179108
201710444
201711545
201712344
20181110
20182245
20183112
20184120


Here is what I have so far:

 

Sales Value = SUM(Table1[SALES])

and my current output:

 

Capture3.PNG

The current output is not exactly what I need, because I can not compare totals.
I can not compare sales for 12 month to 4 ones. It simply makes no sense.

This is my desirabe output:

 

Capture2.PNG

I want my measure to filter MONTHs for the latest year only and compare with the same period previous years.

I do not want any slicers for that. I do not want to show 5-12 months of 2017 because they have no counterparts in 2018.
Each time when Table1 is updated with latest month I would like to have it inclued in my output.


Hope it is clear. Hope someone can help

2 ACCEPTED SOLUTIONS

Accepted Solutions

Re: Dynamic filter on MONTH

Just correcting the previous answer please do the following

 

Create a new column as 

Date = date(Table1[YEAR],Table1[MONTH],"01")

and another column as 

SalesYTD = var maxmonth = month(max(Table1[Date])) return if(Table1[MONTH] <= maxmonth ,Table1[SALES],0)

You can now use the column SalesYTD for your calculation

View solution in original post

Super User
Super User

Re: Dynamic filter on MONTH

Hi @Gordon2,

 

You may refer to my solution here.

 

Hope this helps.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

6 REPLIES 6

Re: Dynamic filter on MONTH

you can recreate the measure as :

 

YTD_SUM =

maxmonth = month(max(date(year,month,"01"))) return 

 

calculate(sum(table1[sales]),month<=maxmonth)

Gordon2 Member
Member

Re: Dynamic filter on MONTH

Hi Nippon,

 

Didn't you miss VAR function in this expresion?

Anyway I get "The MAX function only accepts a column reference as an argument."

In fact YEAR and MONTH columns have no Date formattting.

(custom calendar)

Re: Dynamic filter on MONTH

Just correcting the previous answer please do the following

 

Create a new column as 

Date = date(Table1[YEAR],Table1[MONTH],"01")

and another column as 

SalesYTD = var maxmonth = month(max(Table1[Date])) return if(Table1[MONTH] <= maxmonth ,Table1[SALES],0)

You can now use the column SalesYTD for your calculation

View solution in original post

Super User
Super User

Re: Dynamic filter on MONTH

Hi,

 

I have solved it.  Please allow me some time to share my solution with you.

 

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Super User
Super User

Re: Dynamic filter on MONTH

Hi @Gordon2,

 

You may refer to my solution here.

 

Hope this helps.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

Gordon2 Member
Member

Re: Dynamic filter on MONTH

Both ways works, tnx!

Helpful resources

Announcements
October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Badges

Incoming: New and Improved Badges

Exciting news: We've given our badges an overhaul and added brand news ones.

Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 169 members 2,016 guests
Please welcome our newest community members: