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,

 

Got the file.

 

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

 

Regards,

MFelix

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Yes...on two filters year and month

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

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Hi

       

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