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.
I want to find the difference in sales between months but till the day of today. I wrote the measures below. Sales_till_day measure works. But when I want to find percentage differences, it doesn’t work, however, any error doesn't appear.
sales_till_day = CALCULATE([total sales];FILTER(Table;DAY('Table'[Date])<=DAY([today]));DATESINPERIOD('Table[Date];STARTOFMONTH('Table'[Date]);DAY([today]);DAY))
sales_till_day% =
VAR monthesbefore = CALCULATE(([sales_till_day]);FILTER('Table';MONTH('Table'[Date])<MONTH[today]))
VAR currentmonth = CALCULATE(([sales_till_day]);FILTER('Table';MONTH('Table'[Date])=MONTH[today]))
return
currentmonth/ monthesbefore
After the measures above, I tried to write different DAX with the name MTD. But the result was the same as previous
MTD= CALCULATE(TOTALMTD([total sales];'Table'[Date];FILTER('Table';Day('Table' [Date])<=day([today]))))
mtd% =
VAR monthesbefore = CALCULATE(TOTALMTD([total sales]);'Table'[Date];FILTER('Table';Day('Table' [Date])<=day([today])); FILTER('Table';MONTH('Table'[Date])<MONTH[today]))
VAR currentmonth = CALCULATE(TOTALMTD([total sales]);'Table'[Date];FILTER('Table';Day('Table' [Date])<=day([today])); FILTER('Table';MONTH('Table'[Date])=MONTH[today]))
Even I tried this
VAR MTD= CALCULATE(TOTALMTD([total sales];'Table'[Date];FILTER('Table';Day('Table' [Date])<=day([today]))))
return
IF([MTD]=0;0;DIVIDE(CALCULATE([MTD];'Table'[Date].[Year]=2019);[mtd])-1)
But these all do not help. If you have any suggestion please help with it.
Hi @Anonymous
Have you tried DATEADD like below.
Sales Previous Month = DIVIDE( [Sales], CALCULATE( [Sales], DATEADD( 'Calendar'[Date], -1, MONTH ) ) )
Regards,
Mariusz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thanks for your reply @Mariusz. This is just for one month. With the DAX you wrote I have to write a formula for 12 months. I need each month until the current day. For example, Today is the 21st of June.
I need to see the sales till January 21, February 21, March 21 and so on. And the percentage comparison between June and other months. For example, June sales are 23% higher than January, 3% lower than February, etc.
Hi @Anonymous
Try the below.
VAR _day = DAY( TODAY() ) VAR _salesPrevousMonth = CALCULATE( [Sales], FILTER( DATEADD( 'Calendar'[Date], -1, MONTH ), DAY('Calendar'[Date]) <= _day ) ) VAR _salesCurrentMonth = CALCULATE( [Sales], FILTER( 'Calendar', DAY( 'Calendar'[Date] ) <= _day ) ) RETURN DIVIDE( _salesCurrentMonth, _salesPrevousMonth )
Regards,
Mariusz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Anonymous
DATEADD is a time intelligence function therefore it requires Date Dimension marked as date table.
Have you got one?
Only asking as the error suggest that you have duplicates and or gaps in your dates, witch shoud not happend if you are using date table.
Hope this helps
Mariusz
Yes, there is date column in the table and there are no gaps. But there are duplicate dates because in one day there are several sales. The date is in date format.
Table is like ,
Date Customer name Sale value
20.06.2019 Customer 1 100
20.06.2019 Customer 2 70
21.06.2019 Customer 2 92
21.06.2019 Customer 12 105
21.06.2019 Customer 4 67
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 |
---|---|
112 | |
97 | |
85 | |
68 | |
59 |
User | Count |
---|---|
150 | |
120 | |
100 | |
87 | |
68 |