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,

 

Believe that your issue is not related with the IF but with the way the formula is getting context. Is the Slicer for month from what column or related to what date column?

 

When you have a slicer and are trying to get previous months you need to take the slicer context from your measure this can be made by having the slicer based on an unrelated column of your data table or then add a ALL sintax to overlay the slicer.

 

Can you share some sample data and expected resut?

 

Regards,

MFelix 

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Hi I can share a file...Can you delete after solution..or would drop box link be better 

  • 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