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

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

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

    • MFelix's avatar
      MFelix
      Icon for Super User rankSuper User
      8 years ago
      Share the file trough private message wirth a dropbox link.

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