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 am trying to write a dax query which will show Year on year sales by Year,Quarter,Month,Week,day in tabular model which will be publish to Power BI dashboard.The below code works good till the last year data comparsion. However the numbers are not correct for the current year since it is comparing the data with the sales for the entire last April month. Can you share your views on this query .
For example :
The last year sales for April were $100 dollar howerver till todays' date 14th April the sales were 50$
so the year on year sales for April month should be $50 however the below dax query is showing around $100 which is incorrect.
Year on year Sales:=SUMX (
VALUES ( 'Time'[YearMonthNumber] ),
IF (
CALCULATE (
COUNTROWS (
VALUES ( 'Time'[Date] )
)
)
= CALCULATE (
VALUES ( 'Time'[MonthDays] )
),
CALCULATE (
[Sales],
ALL ( 'Time' ),
FILTER (
ALL ( 'Time'[YearMonthNumber] ),
'Time'[YearMonthNumber]
= EARLIER ( 'Time'[YearMonthNumber] ) - 12
)
),
CALCULATE (
[ Sales],
ALL ( 'Time' ),
CALCULATETABLE (
VALUES ( 'Time'[Financial Day of Month] )
),
FILTER (
ALL ( 'Time'[YearMonthNumber] ),
'Time'[YearMonthNumber]
= EARLIER ( 'Time'[YearMonthNumber] ) - 12
)
)
)
)
Try the following
I am assuming you have a MasterCalendar Table.
1. Create a measure called
FirstDateCurMonth = Calculate ((FirstDate(MasterCalendar[DateKey])),All(MasterCalendar))
2. Create a measure called
LastDateCurMonth = Calculate ((LastDate(MasterCalendar[DateKey])))
3. Create a measure called
LastDatePrevYear = EDATE([LastDateCurMonth],-12)
4. Create a meassure called
FirstDatePrevYear = Date(Year([LastDatePrevYear]),Month([LastDatePrevYear] ),1)
5. Create a meassure called
LastYearSamePeriod = CALCULATE (
[Sales] ,
Datesbetween(MasterCalendar[Datekey], [FirstDatePrevYear],[LastDatePrevYear]) )
Now plot your month, mtd sales and LastYearSamePeriod, it should work.
Note that this solution works on date basis, and not on month basis. So when you try to compare non leap-year Feb month with previous leap year month, your totals for the previous year will be upto 28 Feb only.
If this works for you, please accept it as a solution and also give KUDOS.
Cheers
CheenuSing
From the top of my head, the easier way to do this would be to add a calculated (or not) column to your date table, flagging all dates on or before "today"'s date to TRUE.
Wrap your expression in a CALCULATE expression, with the column you just added as a context argument.
Something like this
CALCULATE( YourExpression , 'Time'[InThePast] )
I tried changing the logic to the below however I am still seeing the Sales are getting calculated for the entire month for April and Quarter 2 and Year 2017.
Year on year sales:=SUMX (
VALUES ( 'Time'[YearMonthNumber] ),
IF (
LASTNONBLANK ( 'Time'[Date], [Gross Sales] ) >= MIN ( 'Time'[Date] ),
CALCULATE (
[Sales],
ALL ( 'Time' ),
FILTER (
ALL ( 'Time'[YearMonthNumber] ),
'Time'[YearMonthNumber]
= EARLIER ( 'Time'[YearMonthNumber] ) - 12
)
)
)
)
This query also does not provide the correct logic. The data looks good till last year.
However the sales for April and Q2 and the year 2017 should be
CALCULATE([gross sales],SAMEPERIODLASTYEAR('Time'[DATE]))
Hi @abhishek_300,
You can try to use below formula: (calculate based on year, week number, weekday)
Measure = var currDate=MAX('Table'[Date]) return SUMX(FILTER(ALL('Table'),YEAR('Table'[Date])=YEAR(currDate)-1&&WEEKNUM('Table'[Date],1)=WEEKNUM(currDate,1)&&WEEKDAY('Table'[Date],1)=WEEKDAY(currDate,1)),[Value])
SAMEPERIODLASTYEAR will return the same date of previous year.
Regards,
Xiaoxin Sheng
I need to calculate percentage change in months from two years, i have years 2016, 2017 and i want to compare similar months from both years, please if you can help?
I am using tabular model 2014. This query won't work in 2014 I guess
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 |
---|---|
110 | |
97 | |
77 | |
63 | |
55 |
User | Count |
---|---|
143 | |
109 | |
89 | |
84 | |
66 |