Forum Discussion
Calcuated Measure Sum per row duplicating
I've got this measure and the outcome is like 50/50 the sum is correct but the drill down by dimension is duplicating the total value in each row
% teste =
VAR Startperiod =
PREVIOUSDAY(FIRSTDATE(DATEADD(DATA[Date],-1*[C. Days of Current Period],DAY)))
VAR Endperiod =
PREVIOUSDAY(LASTDATE(DATEADD(DATA[Date],-1*[C. Days of Current Period],DAY)))
VAR PreviousCost =
CALCULATE(
SUMX(
FILTER(
ALL(DATA),
DATA[Date] >= Startperiod && DATA[Date] <= Endperiod
),
DATA[Total Cost]
)
)
RETURN PreviousCost
Also tried this one
% teste =
VAR Startperiod =
PREVIOUSDAY(FIRSTDATE(DATEADD(DATA[Date],-1*[C. Days of Current Period],DAY)))
VAR Endperiod =
PREVIOUSDAY(LASTDATE(DATEADD(DATA[Date],-1*[C. Days of Current Period],DAY)))
VAR PreviousCost =
CALCULATE([SUM Total Cost],DATESBETWEEN(DATA[Date],Startperiod,Endperiod),ALL(DATA))
RETURN PreviousCost
So the out come should be the 1st option but its the 2nd one
I've tried to do the same calculations by creating measures instead of variables but the outcome is the same.
Thanks!
Bruno_Soares_25 , Join your date with the date table and then use the rest formula on the date table it should work
example
% teste =
VAR Startperiod =
PREVIOUSDAY(FIRSTDATE(DATEADD(Date[Date],-1*[C. Days of Current Period],DAY)))
VAR Endperiod =
PREVIOUSDAY(LASTDATE(DATEADD(Date[Date],-1*[C. Days of Current Period],DAY)))
VAR PreviousCost =
CALCULATE([SUM Total Cost],DATESBETWEEN(Date[Date],Startperiod,Endperiod))
RETURN PreviousCostWhy Time Intelligence Fails - Powerbi 5 Savior Steps for TI :https://youtu.be/OBf0rjpp5Hw
https://amitchandak.medium.com/power-bi-5-key-points-to-make-time-intelligence-successful-bd52912a5bd4
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 :radacad sqlbi My Video Series Appreciate your Kudos.
1 Reply
Bruno_Soares_25 , Join your date with the date table and then use the rest formula on the date table it should work
example
% teste =
VAR Startperiod =
PREVIOUSDAY(FIRSTDATE(DATEADD(Date[Date],-1*[C. Days of Current Period],DAY)))
VAR Endperiod =
PREVIOUSDAY(LASTDATE(DATEADD(Date[Date],-1*[C. Days of Current Period],DAY)))
VAR PreviousCost =
CALCULATE([SUM Total Cost],DATESBETWEEN(Date[Date],Startperiod,Endperiod))
RETURN PreviousCostWhy Time Intelligence Fails - Powerbi 5 Savior Steps for TI :https://youtu.be/OBf0rjpp5Hw
https://amitchandak.medium.com/power-bi-5-key-points-to-make-time-intelligence-successful-bd52912a5bd4
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 :radacad sqlbi My Video Series Appreciate your Kudos.