Forum Discussion
Nested if error in dax formula
Hi experts
I have the following VAR was formula which does not work with certain visuals. I have a year slicer and a month slicer when I select year then month the result should be 112 months back from the month date. I have been told that the error lies within the nested if statement of the return...cannot see wood for trees..
Datefilter12Month:=
VAR MonthsToLookBack = 12
VAR DataTableDate = MIN('Date'[Date])
VAR DateTableDate = MIN('DateFilter'[Full Date])
VAR DateAddAlternative = EDATE(DateTableDate,-MonthsToLookBack) RETURN IF( (DataTableDate <= DateTableDate) && (DataTableDate > DateAddAlternative) , 1 )
Hi Anonymous,
Add the following measure yo your model:
Datefilter12Month = VAR MonthsToLookBack = 12 VAR DataTableDate = MAX ( 'DimDate'[Date] ) RETURN CALCULATE ( SUM ( Sales[Sales] ); DATESINPERIOD ( dimDate[Date]; DataTableDate; - MonthsToLookBack; MONTH ) )See attach a PBIX file I prepared with a similar setup than yours.
Regards.
MFelix
10 Replies
Hi Anonymous,
Believe that your issue is not related with the IF but with the way the formula is getting context. Is the Slicer for month from what column or related to what date column?
When you have a slicer and are trying to get previous months you need to take the slicer context from your measure this can be made by having the slicer based on an unrelated column of your data table or then add a ALL sintax to overlay the slicer.
Can you share some sample data and expected resut?
Regards,
MFelix
- Share the file trough private message wirth a dropbox link.
Regards
MFelix