Dynamic filter on MONTH

[Anonymous]

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

YEAR	MONTH	SALES
2017	1	100
2017	2	222
2017	3	102
2017	4	123
2017	5	322
2017	6	105
2017	7	106
2017	8	323
2017	9	108
2017	10	444
2017	11	545
2017	12	344
2018	1	110
2018	2	245
2018	3	112
2018	4	120

Here is what I have so far:

Sales Value = SUM(Table1[SALES])

and my current output:

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:

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

[NipponSahore · Accepted solution]

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

[Ashish_Mathur · Accepted solution]

Hi @Anonymous,

You may refer to my solution here.

Hope this helps.

[Anonymous]

Both ways works, tnx!

[Ashish_Mathur]

Hi,

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

[NipponSahore]

you can recreate the measure as :

YTD_SUM =

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

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

[Anonymous]

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)

[NipponSahore · Accepted solution]

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