Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

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 ba...
Need Help
  • MFelix's avatar
    MFelix
    7 years ago

    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

MFelix's avatar
MFelix
Super User
7 years ago

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's avatar
Anonymous
Not applicable
7 years ago

Many thanks sir... let me test

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

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