Everything is possible with DAX but first for your measure to work you need a new Date Table and then have an active relantionship to the sales table. Up to now you cannot use DAX time intelligence funtions without date table.
There a few ways to create it. Import from excel or DB , create one with PowerQuery or create with DAX calculate table.
However, I haven't been able yet to use calendar functions.
I created the DateTable with one column ([Date]) and a relationship between that column with the date-column in my other table, however, no results are shown if I try to use a function like PreviousMonth.
Generally when you have a date table (or any lookup table), you want to hide the lookup values in the fact table and use the values in the lookup table exclusively.
For example, if you have two fact tables linked to a lookup table, and then filter using values from one of the fact tables rather than the lookup, the other fact table won't be filtered.
I aim to hide every column in my fact tables (leaving only measures), so I am never tempted to filter from a fact table.
(Not that it matters since you've solved the problem, I just wanted to add the additional context since I was once confused about lookup tables and why I wouldn't just use the values in the fact table since they were right there)
I am not sure about it, but as per my understanding the syntax of of Calculate 'Calculate(Expression,<filter expression1>,<filter expression2>....)'. Since you have applied a filter using date table and not using fact, this might be a case it is not working.
But as a best practice, we use the filters from dimension table not from fact table.
Can you please elaborate your requiremnt why do you want to use slicer from fact table?
I am facing similar challenges. I have a Calendar table which is populated on the range of dates betwen minimum of Sales Date and maximum of sales date. The Calendar table thus built has all the contiguous dates. Say between 01/07/2013 and 15/05/2016.
I want to compute the Sales for the period '01/04/2016' and '15/04/2016' and compare it with the sales for the period '01/05/206' and '15/05/2016'. A variant of month-on-month sales. When I use previous month in the expression it gives me total of entire Apr 2016 and not just the 1-15 of Apr.
How to achieve this ?
Also when I use a measure CurYr = MAXX(Calendar,Calendar[FullDate]), I get the result as 2016 which is correct. But I am unable to use this measure in any other comparison inside a calculate expression and give me error like A function Caculate has been used in a true false expression that is used as a table filter expresion. This is not allowed.
Thank you in advance for your help.
Did I answer your question? Mark my post as a solution and also give KUDOS !