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

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 Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

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: 68 members 938 guests
Please welcome our newest community members: