Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Brysonds
Helper III
Helper III

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. 

 

I know what calculation I can use and how to achieve most of this, but I am stuck on how to restrict the calculation to 2 values in the slicer.

 

So simply put, I am trying to achieve this: IF PERIODS SELECTED = JAN AND FEB, THEN (JAN - FEB)

 

I've tried several variations of this formula, but I am stuck on the AND part of the clause.

 

TD Variance = 
SWITCH(TRUE(),
VALUES('ABCD Customer YTD'[Period])=DATE(2019,1,1) && VALUES('ABCD Customer YTD'[Period])=DATE(2019,2,1), 
    CALCULATE(
	    SUM('ABCD Customer YTD'[(YTD) Sales Amount]),
	    'ABCD Customer YTD'[Period] IN { DATE(2019, 1, 1) }
    ) - 
    CALCULATE(
	    SUM('ABCD Customer YTD'[(YTD) Sales Amount]),
	    'ABCD Customer YTD'[Period] IN { DATE(2019, 2, 1) }
    ), 
//Additional combinations will go here// BLANK())

 

 

 

1 ACCEPTED SOLUTION
jdbuchanan71
Super User
Super User

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

YearMonthDiff.jpg

 

View solution in original post

3 REPLIES 3
jdbuchanan71
Super User
Super User

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

YearMonthDiff.jpg

 

Exaclty what was needed - thank you so much!

Nathaniel_C
Super User
Super User

@Brysonds ,

Not sure that I get this, but if MIN(Date) and MAX(Date) or FIRSTDATE(Date) and LASTDATE(Date) when selected by slicer period should work.

 

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.