Nested if error in dax formula

[Anonymous]

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..

Spoiler

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 ) 

[MFelix]

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 

[Anonymous]

Hi I can share a file...Can you delete after solution..or would drop box link be better 

[MFelix]

Share the file trough private message wirth a dropbox link.

Regards
MFelix

[Anonymous]

Sent.

Can you confirm receipt. Also from the table that does not have sales date you'll  need to create a month and year column to that data set. Or the sales table data set. Also as I was in a rush to create a sample file did not have time to label tables correctly- sorry.

[MFelix]

Hi @Anonymous,

Got the file.

One confirmation you want to calculate the sum of sales 12 months back from the selected date correct?

Regards,

MFelix

[Anonymous]

Yes...on two filters year and month

[MFelix · Accepted solution]

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

[Anonymous]

Hi

Not sure do you want me to close question and re post. or is this still open?

[Anonymous]

Many thanks sir... let me test

[Anonymous]

MFelix..

Just looked at the file...works. but if i change the card to a visual i bar chart clustered column. i want to be able to show 12 periods.. is this possible...