Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi,
I am trying to find a way to calculate the sum of a value for the last day of the previous year.
For example: If I choose 31.05.2017 then the sum for 31.12.2016. should be calculated.
Hope somebody can help me. Thanks.
Christoph
Solved! Go to Solution.
Hi @Chrism123,
You said you choose 31.05.2017, you have a slicer in your report? If it is, I assume that the slicer including Table[date] column. You can create the measure using the formula.
sum = CALCULATE ( SUM ( Table[value] ), FILTER ( Table, Table[date] = DATE ( YEAR ( SELECTEDVALUE ( Table[date] ) )-1, 12, 31 ) ) )
If this still doesn't resolve your issue, please post more details for further analysis.
Best Regards,
Angelia
Hi @Chrism123
Try using this. Just added "ALL"
sum = CALCULATE ( SUM ( Table[value] ), FILTER ( All(Table), Table[date] = DATE ( YEAR ( SELECTEDVALUE ( Table[date] ) )-1, 12, 31 ) ) )
Hi @Chrism123,
You said you choose 31.05.2017, you have a slicer in your report? If it is, I assume that the slicer including Table[date] column. You can create the measure using the formula.
sum = CALCULATE ( SUM ( Table[value] ), FILTER ( Table, Table[date] = DATE ( YEAR ( SELECTEDVALUE ( Table[date] ) )-1, 12, 31 ) ) )
If this still doesn't resolve your issue, please post more details for further analysis.
Best Regards,
Angelia
Thanks for your replay Angelia.
My problem is: I have a sales amount for each day of the year and I need to subtract another amount from those amounts (which should be calculated from 31.12. of the previous year.
Final amount = Sales Amount per 31.07.2017 - ValueXY per 31.12.2016
Final amount = Sales Amount per 30.06.2016 - ValueXY per 31.12.2015
Final amount = Sales Amount per 30.11.2016 - ValueXY per 31.12.2015
I just don't know how I can calculate the ValueXY for 31.12.PreviousYear (compared to the Sales Amount Date).
Christoph
Hi @Chrism123,
Have you resolved your issue? If you have, welcome to share your solution or mark the right reply as answer. More people will benefit from here.
Best Regards,
Angelia
Angelia @v-huizhn-msft probably missed the -1
Table[date] = DATE ( YEAR ( SELECTEDVALUE ( Table[date] ) )-1, 12, 31 )
The problem with this solution is:
It only subtracts the value for XY when I chose the 31.12.. But it should also subtract XY when I chose another date.
Hi @Chrism123
Try using this. Just added "ALL"
sum = CALCULATE ( SUM ( Table[value] ), FILTER ( All(Table), Table[date] = DATE ( YEAR ( SELECTEDVALUE ( Table[date] ) )-1, 12, 31 ) ) )
Hi,
I have the same requirement. When I choose any date in 2018, the previous year cummulative amount should be on Dec,31st 2017. In the same way if I choose the date in 2017 then the previous year amount should be on Dec,31st 2016.
I am not able to get the result using this formula. Can someone help me?
Regards,
Ajay Emmadi
I have also same requirement . @Chrism123 have you find the solution @Anonymous ?
I was able to get this to work using the following:
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
118 | |
101 | |
71 | |
61 |