Last day of previous year

[Chrism123]

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

[v-huizhn-msft · Accepted 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

[v-huizhn-msft · Accepted 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

[Chrism123]

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

[v-huizhn-msft]

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

[Zubair_Muhammad]

Angelia @v-huizhn-msft probably missed the -1

Table[date] = DATE ( YEAR ( SELECTEDVALUE ( Table[date] ) )-1, 12, 31 )

[Chrism123]

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.

[Zubair_Muhammad · Accepted solution]

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 )
    )
)

[Anonymous]

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

[Anonymous]

I have also same requirement . @Chrism123  have you find the solution @Anonymous ?

[Anonymous]

I was able to get this to work using the following:

PYE_ACCT_997v2 = CALCULATE(MAX('Query1'[ACCT_997]), PREVIOUSYEAR('Dates'[Cycle Date]))