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

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

      • Anonymous's avatar
        Anonymous
        Not applicable
        7 years ago

        Many thanks sir... let me test