Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
105 | |
94 | |
75 | |
63 | |
62 |
User | Count |
---|---|
137 | |
105 | |
104 | |
80 | |
63 |