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
Share the file trough private message wirth a dropbox link.

Regards
MFelix
Anonymous's avatar
Anonymous
Not applicable
7 years ago

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