DAX Calculate dyanamic measure to compare YoY in graphic chart

DAX DAX Calculations
1

Hi all,

This is my first time on the forum :wink:

I am struggling with this scenario. I am trying to figure out a measure which will enable to visualize Sales from a specific period of time (Eg: month and year ‘Oct 2019’) of that same period of my last 5 years (‘Oct 2018’, (‘Oct 2017’ …) in a graphic chart ( one column value for each year). These values shoud dynamically change when choosing different period (month and year). I mean, if I change the value on the slicer to Sept 2018, graphic will return values for Sept 2017, Sept 2016…. Sept 2014.

I have tried to create a disconnected date table and apply this measure but is is giving me wrong result. It returns all year sales instead of sales for the month selected on the slicer (see pbix attached)

Last 3 years sales = 
VAR CurrentDate = MAX ( 'Dates Disconnected'[Date] ) 
VAR PrevDate = DATE ( YEAR ( CurrentDate ); MONTH ( CurrentDate ) -36; DAY ( CurrentDate ) ) 
VAR Result = CALCULATE (
   [Total Sales];
    FILTER (
        Sales;
        Sales[OrderDate] >= PrevDate 
       && Sales[OrderDate] <= CurrentDate
    )
) 
RETURN Result

Forum_EnterpiseDNA.pbix (547.1 KB)

Last5years example 2
Last5years example 2873×679 17.1 KB
Last5years example
Last5years example858×642 17.3 KB

Thanks in advanced :wink:

2

Hi @judy_fmf,

Welcome to the Forum!
And big thanks for your detailed explanation and supplying a sample PBIX :+1:

I’ve changed the Date table M code you used to the Extended Date table you can find here

Great job on setting up a disconnected date table, that’s exactly what you need in a scenario like this. Just one remark you had placed the Year from fact table Sales on the axis of your bar chart so I changed that to Year from the Dates table. To avoid wrong selections like these, I always recommend to ‘hide in report view’ all fields from fact tables that are keys to- or attributes of dimensions.

Next I created this measure:

Sales by Year and Month = 
VAR ThisYear = SELECTEDVALUE( 'Dates Disconnected'[YearOffset] )
VAR YearRange = GENERATESERIES( ThisYear -2, ThisYear, 1 )
VAR ThisMonth = SELECTEDVALUE( 'Dates Disconnected'[MonthOfYear] )
RETURN

IF( SELECTEDVALUE( Dates[YearOffset] ) IN YearRange,
        CALCULATE( [Total Sales],
            FILTER( ALLSELECTED( Dates),
                Dates[Year] = SELECTEDVALUE( Dates[Year] ) &&
                Dates[MonthOfYear] = ThisMonth )
        ), 
        CALCULATE( [Total Sales],
            FILTER( SUMMARIZE( Dates, Dates[YearOffset], Dates[MonthOfYear]),
                [YearOffset] IN YearRange &&
                [MonthOfYear] = ThisMonth ))
    )

.
With this result.

image
image1135×251 13 KB

.
I hope this is helpful. Here’s my sample PBIX.
eDNA - Last 3 yrs sales for selected month.pbix (547.1 KB)

1 Like
3

Hi @judy_fmf
PFB solution for your requirement.
I’m just showing past 3years data. If you want you can change the Variable PY.

3Y Same period =
VAR SM =
SELECTEDVALUE ( ‘Dates Disconnected’[MonthName] )
VAR SY =
SELECTEDVALUE ( ‘Dates Disconnected’[Year] )
VAR PY = 3
VAR Sameperiod =
FILTER (
Dates,
( Dates[Year] > SY - PY
&& Dates[Year] <= SY )
&& Dates[MonthName] = SM
)
RETURN
CALCULATE ( [Total Sales], Sameperiod )

3Y SamePeriod
3Y SamePeriod969×581 46.3 KB

2 Likes
4

I was definitely overthinking it… time for a break :crazy_face:

Thanks for sharing @Rajesh

5

@Melissa You’re welcome

6

Thank both of you for your quick response.

I have already tried @Rajesh solution and works perfectly fine. It is such a simple and perfect solution…

I am now trying to understand Melissa’s. It’s a little bit more complicated for me but I want to try to understand it. By the way, @Melissa thanks for the extended date table script, it’s fantastic :wink:

7

You’re welcome Judy.

Stick with @Rajesh solution it’s the best of the two.

1 Like