Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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,
Solved! Go to 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
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
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
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
64 |
User | Count |
---|---|
135 | |
118 | |
101 | |
71 | |
61 |