Hello,
I am working with a fact table where there's a column for sales and one column for the budgetted sales, with the timespan from 2012-2022. The budget column is filled out till December 2022, but currently I have only received data until October for actual sales. As there is no data for November/December 2022 yet I would like to filter out these months, but I can't seem to find a filter where I can filter out specific months in a specific year.
I have tried to create my own formulas, but both have pros/cons.
My first one is filtering out rows in my budgetted sales column if there is 0 in the same row under sales column.
Measure =
CALCULATE(
SUM(Table[Budget]),
FILTER(
Table,
NOT(Table[Sales] IN {0})
)
)
However, there's a running risk of the input of sales being 0 once and then it will be filtered out when it should be included.
The second solution is using an IF filter. First I create two columns in the date table. First column identifies end of month day, then in the second column I use the IF function to create a boolean filter which I then apply in the dashboard.
LastDateofMonth = ENDOFMONTH(dDato[Date])
IsFutureMonth = IF(TODAY() > dDato[LastDateofMonth], 1, 0)
However, the issue here is that if I receive the data for sales later than said month (e.g. I am still lacking November data and we're in December), the filter will still include November in my model and obscure the numbers.
Thus, I was wondering if there is a better/simpler way of filtering out specific months in a year.
Thank you,
Renate
