Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
I want to plot some measurements on a line chart.
Measures refer to the period selected in a filter on the page.
Then the measure referring to the value of the selected date is plotted, the measure related to the same previous period, and the same thing for 2 and 3 previous periods.
Below is an image with these measurements plotted with static values (-7"days"(same as previous period), -14"days"(same as two previous period), -21"days"(same as three previous period).
A week is selected, and I want to take the previous 3 weeks and plot it on the same chart.
Criados P-3 = CALCULATE(
[total_negocios],
DATESBETWEEN(dCalendario[Date], FIRSTDATE(DATEADD(dCalendario[Date], -21, DAY)), FIRSTDATE(DATEADD(dCalendario[Date], -21, DAY)))
)
I'm calculating how many days there are between 2 dates that are filtered on the page.
The value returned is correct, but when I put this date into a measure (this measure I want to plot on the chart), it comes with a value of -1.
I know this because the measurement is in relation to 1 day prior to the week selected in the filter, in this case the measurement in dark blue (Criados P-1), you can see that the value that was static (-7) is supposed to be coming from the other measure [Dias_entre_datas], which has a value of -7 but when placed inside the DATEADD function, it is -1.
Below is the measure I did to get the number of days between the dates selected in the filter.
And also as it is getting the value -1. (Plotted on the chart as dark blue as well).
However, in Measure Criados P-2, I'm using the measure and multiplying by 2 to be relative to 2 periods (2 * -7 = -14), and it's going right, see that in the previous chart the orange line is exactly equal to this new graph with the orange line as well.
The error is happening only when I am going to do it in the previous period.
(I've tried putting "1 * Dias_entre_datas", but it's -1 too.
Dias_entre_datas =
VAR valor = DATEDIFF(LASTDATE(dCalendario[Date]), PREVIOUSDAY(FIRSTDATE(dCalendario[Date])), DAY)
RETURN
valor
Criados P-2 = CALCULATE(
[total_negocios],
DATESBETWEEN(dCalendario[Date], FIRSTDATE(DATEADD(dCalendario[Date], 2*[Dias_entre_datas], DAY)), FIRSTDATE(DATEADD(dCalendario[Date], 2*[Dias_entre_datas], DAY)))
)
Does anyone have any ideas?
Hi @GustavoBorges ,
I think the var lead to the wrong result. Please note that the var will ignore any filters when you do some filter to it under "return".
Change the Dias_entre_datas like this:
Dias_entre_datas =
DATEDIFF(LASTDATE(dCalendario[Date]), PREVIOUSDAY(FIRSTDATE(dCalendario[Date])), DAY)
Best Regards
Community Support Team _ chenwu zhu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
The same here
Criados P-1 = CALCULATE(
[total_negocios],
DATESBETWEEN(dCalendario[Date], LASTDATE(DATEADD(dCalendario[Date], [Dias_entre_datas], DAY)), PREVIOUSDAY(dCalendario[Date]))
)
Incidentally, if I multiply the [Dias_entre_datas] by "-1" inside the DATESBETWEEN, it works by inverting from "-1" to "1", as in the image below
Criados P-1 = CALCULATE(
[total_negocios],
DATESBETWEEN(dCalendario[Date], FIRSTDATE(DATEADD(dCalendario[Date], -1*[Dias_entre_datas], DAY)), FIRSTDATE(DATEADD(dCalendario[Date], -1*[Dias_entre_datas], DAY)))
)
User | Count |
---|---|
98 | |
90 | |
82 | |
73 | |
67 |
User | Count |
---|---|
115 | |
102 | |
98 | |
71 | |
66 |