Individual Visual Filtering & MoM Comparison

[Blackworms]

Hi everyone,

First of, thanks for all the help on the forums; however, I have a problem which I couldn't resolve at all since I am kind of beginner with DAX.

My dataset has unique Sales Year-Month-Day values from 01.01.2016 to 05.20.2017 in date format and was related with a Calendar Date column which ends at 05.20.2017 as well since it's the last data collection date.

First I was struggling to compare MoM for 2016 and 2017 sales. For example, May of 2017 contains only 20 days of data and I wanted to compare the same date range for May of 2016; however, it was fetching all the 2016 data till the end of the year. I solved that issue with Mr. Owen's explanation under this topic. 

For this year sales I used: 

This Year Sales = CALCULATE(SUM([SalesRevenue]);DATESYTD('Calendar'[Date].[Date]))

And for last year sales:

Last Year Sales = 
VAR DataMaxDate =
    CALCULATE ( MAX ( 'Calendar'[Date] ); ALL ( 'Calendar'[Date] ) )
RETURN
    CALCULATE (
        [This Year Sales];
			SAMEPERIODLASTYEAR (
            INTERSECT (
                VALUES ( 'Calendar'[Date].[Date] );
                DATESBETWEEN ( 'Calendar'[Date].[Date]; BLANK (); DataMaxDate )
            )
        )
    )

Since my intention was comparing apples to apples, this was the result I was looking for. I finally was able compare the first 20 days of May 2016 and first 20 days of May 2017. 

However, now I am not able to visually filter any of the months. I mean, when I click Last Year Sales, it's automatically accumulating This Year Sales as well. Please see below:

My intention is:

1. To be able to individually filter every year's sales like I was able to before.

2. If I cannot filter individually because of the formulas I used, I am kind of looking for a new way to compare last year's and this year's sales. Like I mentioned, last data date must be taken into account for last year's datas.

3. I created a different Calendar.[Date] and columns related with my sales date data. (Format is in date like 01.01.2016)

Thanks for all the help, rgrds,

[v-huizhn-msft]

Hi @Blackworms,

Your issue has been resolved? If not, please post the feedback. If you have, please mark the right reply as answer.

Thanks,
Angelia

[v-huizhn-msft · Accepted solution]

Hi @Blackworms,

You should calculate this year sales use DATESMTD function.

This Year Sales = CALCULATE(SUM([SalesRevenue]);DATESMTD('Calendar'[Date].[Date]))

As I tested, I create a relationship between Calendar and Sales table, and create two measure usingthe following formulas, it works fine.

This Year Sales = TOTALMTD(SUM(Sales[SALE]),'Calendar'[DATE])

Last Year Sales = 
VAR DataMaxDate =
    CALCULATE ( MAX('Calendar'[Date] ), ALL ('Calendar'[Date]) )
RETURN
    CALCULATE (
        [This Year Sales],
			SAMEPERIODLASTYEAR (
            INTERSECT (
                VALUES ( 'Calendar'[Date]),
                DATESBETWEEN ( 'Calendar'[Date],BLANK (), DataMaxDate )
            )
        )
    )

Please see the following screenshot, when I select particular month, it still shows correctly.



Thanks,
Angelia