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 Guys,
I an facing some issue in Dax.
I want to calculate Year over Year Change for YTD. Like for Feb'20 i have data till feb so I want in YOY change it should select data for 2019 till feb only.
But somehow it is taking taking Full year 2019 data.
Can you pls help me out here.
Solved! Go to Solution.
@SK16 , check the way I suggested to force either Today or max date as a filter in YTD.
You have to force additional date filter on top of YTD
Hi @SK16
You can add an If condition to check if the fact table "sales" in my case is empty.
Sales YTD =
IF(
NOT ISEMPTY( Sales ),
CALCULATE(
[Sales],
DATESYTD( 'Calendar'[Date] )
)
)
Sales YTD LY =
CALCULATE(
[Sales YTD],
SAMEPERIODLASTYEAR( 'Calendar'[Date] )
)
Hi Mariusz,
Thanks for your help buddy.
I got the issue which I am facing in my Dax i.e if I dont select any Month then it is taking Full Previous Year but if select months from the slicer then it is showing the correct value.
But what I want is that without selecting any month it should consider latest month for YTD.
@SK16 , check the way I suggested to force either Today or max date as a filter in YTD.
You have to force additional date filter on top of YTD
Thanks Amit,
It really helped
Hi Mariusz,
Thanks for your help buddy.
I got the issue which I am facing in my Dax i.e if if dont select any Month then it is taking Full Previous Year but if I any select months from the slicer then it is showing the correct value.
But I want without selecting any month i should consider lates month for YTD.
This will work with date calendar.
If you have slicer to pass dates
YTD QTY = TOTALYTD(Sum('order'[Qty]),'Date'[Date])
LYTD QTY = TOTALYTD(Sum('order'[Qty]),dateadd('Date'[Date],-1,year))
Using Today
YTD QTY forced=
var _max = today()
return
calculate(Sum('order'[Qty]),DATESYTD('Date'[Date]),'Date'[Date]<=_max)
//calculate(TOTALYTD(Sum('order'[Qty]),'Date'[Date]),filter('Date','Date'[Date]<=_max))
LYTD QTY forced=
var _max = date(year(today())-1,month(today()),day(today()))
return
CALCULATE(Sum('order'[Qty]),DATESYTD(dateadd('Date'[Date],-1,year)),'Date'[Date]<=_max)
//TOTALYTD(Sum('order'[Qty]),dateadd('Date'[Date],-1,year),'Date'[Date]<=_max)
Using Max date
YTD QTY forced=
var _max = maxx('order',[Order date])
return
calculate(Sum('order'[Qty]),DATESYTD('Date'[Date]),'Date'[Date]<=_max)
//calculate(TOTALYTD(Sum('order'[Qty]),'Date'[Date]),filter('Date','Date'[Date]<=_max))
LYTD QTY forced=
var _max1 =maxx('order',[Order date])
var _max = date(year(_max1)-1,month(_max1),day(_max1))
return
CALCULATE(Sum('order'[Qty]),DATESYTD(dateadd('Date'[Date],-1,year)),'Date'[Date]<=_max)
//TOTALYTD(Sum('order'[Qty]),dateadd('Date'[Date],-1,year),'Date'[Date]<=_max)
To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/
I am hosting a webinar on 25th April on Power BI, Check Details - https://www.linkedin.com/posts/amitchandak78_webinar-tech-techforgood-activity-6658266754378231808-y...
I am facing same issue for Month to date.
Can you please help me out here.
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 |
---|---|
43 | |
23 | |
21 | |
15 | |
15 |
User | Count |
---|---|
46 | |
31 | |
30 | |
18 | |
18 |