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...
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
you can recreate the measure as :
YTD_SUM =
maxmonth = month(max(date(year,month,"01"))) return
calculate(sum(table1[sales]),month<=maxmonth)
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)
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