Forum Discussion
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
| 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
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
6 Replies
you can recreate the measure as :
YTD_SUM =
maxmonth = month(max(date(year,month,"01"))) return
calculate(sum(table1[sales]),month<=maxmonth)
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
Hi,
I have solved it. Please allow me some time to share my solution with you.