Forum Discussion

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

Yes...on two filters year and month

MFelix's avatar
MFelix
Icon for Super User rankSuper User
8 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
    8 years ago

    Many thanks sir... let me test

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 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...

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    Hi

     

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