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 team, I'm having a headache with this problem, I need to calculate a column with variance in percentage to previous date, BUT with non consecutive dates.
I'm getting this using
Var%PreviousDate =
((SUM('cnv rends-patrim-varcp'[CantCPActual]) /
CALCULATE(
SUM('cnv rends-patrim-varcp'[CantCPActual]);
PREVIOUSDAY('cnv rends-patrim-varcp'[fecha])
))-1)*100
For example, the previous date for 19/03-16/03 doesn't work because the formula is using 18/03 instead of 16/03.
I should be able to get the spreadsheet aproach, look:
Well, that's all, I hope someone can help, I didn't find this issue on the forums.
Regards,
Ariel
Solved! Go to Solution.
Hi try wiyh:
Var%PreviousDate = VAR fdate = SELECTEDVALUE ( Tabla1[Date] ) VAR ff = CALCULATE ( MAX ( Tabla1[Date] ); Tabla1[Date] < fdate ) RETURN ( ( SUM ( Tabla1[CantCPActual] ) / CALCULATE ( SUM ( Tabla1[CantCPActual] ); FILTER ( ALL ( Tabla1 ); Tabla1[Date] = ff ) ) - 1 ) * 100 )
Regards
Victor
Lima - Peru
Hi,
Share the link from where i can download your PBI file.
Sorry, I'm new here is the link to the test pbix
https://drive.google.com/open?id=1SpXqIgStbgwuvwLRhloCw_30aJ7_qww8
Hi try wiyh:
Var%PreviousDate = VAR fdate = SELECTEDVALUE ( Tabla1[Date] ) VAR ff = CALCULATE ( MAX ( Tabla1[Date] ); Tabla1[Date] < fdate ) RETURN ( ( SUM ( Tabla1[CantCPActual] ) / CALCULATE ( SUM ( Tabla1[CantCPActual] ); FILTER ( ALL ( Tabla1 ); Tabla1[Date] = ff ) ) - 1 ) * 100 )
Regards
Victor
Lima - Peru
Thank you very much @Vvelarde, it worked perfectly!
I'm still have a lot to learn about DAX.
To get the previous day, you want to find the MAX of all dates that are less than the current date. Tough to be more specific without data pasted in a way that can be copied and pasted easily (non-image). But, these two links might help, see what I do with MTBF in this article:
See my article on Mean Time Before Failure (MTBF) which uses EARLIER: http://community.powerbi.com/t5/Community-Blog/Mean-Time-Between-Failure-MTBF-and-Power-BI/ba-p/3395...
Also, see this article:
Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
sorry, here is the link to the test pbix
https://drive.google.com/open?id=1SpXqIgStbgwuvwLRhloCw_30aJ7_qww8
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 |
---|---|
118 | |
104 | |
77 | |
73 | |
52 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |