Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi guys!
I am having a calculation problem for the difference in dates between the steps of my data model.
Here's the model:
I need to calculate the difference in dates between each step, for that I made a measure that didn't work entirely.
Measure created:
This measure works when I don't use the step in the vision:
We can see above that the calculation is done correctly between the dates.
When I use the step in the table the calculation stops happening:
Can someone help me solve this problem?
Solved! Go to Solution.
you can try to create a column
Column =
VAR last=maxx(FILTER('Table','Table'[DT_atualizacao]<EARLIER('Table'[DT_atualizacao])&&'Table'[personalizar]=EARLIER('Table'[personalizar])),'Table'[DT_atualizacao])
return if(ISBLANK(last),0,'Table'[DT_atualizacao]-last)
Proud to be a Super User!
please try this
Measure =
var last=maxx(FILTER(all('Table'),'Table'[DT_atualizacao]<SELECTEDVALUE('Table'[DT_atualizacao])),'Table'[DT_atualizacao])
return if(ISFILTERED('Table'[DT_atualizacao]),if(ISBLANK(last),0,SELECTEDVALUE('Table'[DT_atualizacao])-MAXX(FILTER(all('Table'),'Table'[DT_atualizacao]=last),'Table'[DT_atualizacao])),CALCULATE(max('Table'[DT_atualizacao]),all('Table'))-CALCULATE(MIN('Table'[DT_atualizacao]),ALL('Table')))
Proud to be a Super User!
@ryan_mayu
Almost!
Now, the only thing that is still wrong is that we are calculating a difference in days for the start date of the process.
See below:
Since day 23/07/2019 is the first day of the process, we should not calculate the previous day.
I think it would be zero.
Measure =
var last=maxx(FILTER(all('Table'),'Table'[DT_atualizacao]<SELECTEDVALUE('Table'[DT_atualizacao])),'Table'[DT_atualizacao])
return if(ISFILTERED('Table'[DT_atualizacao]),if(ISBLANK(last),0,SELECTEDVALUE('Table'[DT_atualizacao])-MAXX(FILTER(all('Table'),'Table'[DT_atualizacao]=last),'Table'[DT_atualizacao])),CALCULATE(max('Table'[DT_atualizacao]),all('Table'))-CALCULATE(MIN('Table'[DT_atualizacao]),ALL('Table')))
In the DAX, iI checked if it is the earliest date, if it is , then display 0. please have a try.
Proud to be a Super User!
@ryan_mayu
I used your mesure, see below:
Is this because there is more data in the model? See below: (this is my full model)
you can try to create a column
Column =
VAR last=maxx(FILTER('Table','Table'[DT_atualizacao]<EARLIER('Table'[DT_atualizacao])&&'Table'[personalizar]=EARLIER('Table'[personalizar])),'Table'[DT_atualizacao])
return if(ISBLANK(last),0,'Table'[DT_atualizacao]-last)
Proud to be a Super User!
Measure =
var last=maxx(FILTER(all('Table'),'Table'[DT_atualizacao]<SELECTEDVALUE('Table'[DT_atualizacao])),'Table'[DT_atualizacao])
return if(ISFILTERED('Table'[DT_atualizacao]),if(ISBLANK(last),0,SELECTEDVALUE('Table'[DT_atualizacao])-MAXX(FILTER(all('Table'),'Table'[DT_atualizacao]=last),'Table'[DT_atualizacao])),CALCULATE(max('Table'[DT_atualizacao]),all('Table'))-CALCULATE(MIN('Table'[DT_atualizacao]),ALL('Table')))
In the DAX, iI checked if it is the earliest date, if it is , then display 0. please have a try.
Proud to be a Super User!
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 |
---|---|
109 | |
102 | |
86 | |
77 | |
70 |
User | Count |
---|---|
120 | |
108 | |
98 | |
83 | |
77 |