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
Blackworms
Frequent Visitor

Individual Visual Filtering & MoM Comparison

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,

1 ACCEPTED SOLUTION
v-huizhn-msft
Employee
Employee

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.

1.PNG2.PNG

Thanks,
Angelia

View solution in original post

2 REPLIES 2
v-huizhn-msft
Employee
Employee

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
Employee
Employee

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.

1.PNG2.PNG

Thanks,
Angelia

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.