Forum Discussion

Brysonds's avatar
Brysonds
Helper III
6 years ago
Solved

Calculation based on AND of selected filter values

Hi - I want to create a variance calculation based on the combination of 2 values within a single date slicer. The calculation is different for each combination since the months will be different.  ...
  • jdbuchanan71's avatar
    6 years ago

    Hello Brysonds 

    If I am understanding correctly this is fairly straight forward with a date table that contains a column with the year and month combined into a single number.  This DAX code will give us a simple date table to use.  Enter it in modeling > new table.

     

    Dates = 
    VAR DateRange = CALENDARAUTO()
    
    RETURN 
    ADDCOLUMNS(
        DateRange,
        "Year",YEAR([Date]),
        "Month",FORMAT([Date],"mmmm"),
        "Year Month", FORMAT([Date],"yyyy-mmmm"),
        "YearMonthSort",YEAR([Date])*100 + MONTH([Date]),
        "ShortName",FORMAT([Date],"ddd"),
        "IsWeekDay", NOT WEEKDAY( [Date] ) IN {1,7}
    )

    You would join this into your model by linking Dates[Date] > 'ABCD Customer YTD'[Period].

    We will want a measure that just sums the amount.

    Sales Amount = SUM('ABCD Customer YTD'[(YTD) Sales Amount])

    Then this measure will calculate the lowest Year Month and the highest year month and give us the difference.

    Difference = 
    VAR FirstMonth = MIN ( 'Dates'[YearMonthSort] )
    VAR LastMonth = MAX ( 'Dates'[YearMonthSort] )
    VAR FirstMonthAmt = CALCULATE( [Sales Amount], 'Dates'[YearMonthSort] = FirstMonth )
    VAR LastMonthAmt = CALCULATE( [Sales Amount], 'Dates'[YearMonthSort] = LastMonth )
    RETURN
    FirstMonthAmt - LastMonthAmt