Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Chrism123
Frequent Visitor

Last day of previous year

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

2 ACCEPTED SOLUTIONS
v-huizhn-msft
Employee
Employee

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

View solution in original post

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

Regards
Zubair

Please try my custom visuals

View solution in original post

9 REPLIES 9
v-huizhn-msft
Employee
Employee

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 )

Smiley Happy


Regards
Zubair

Please try my custom visuals

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

Regards
Zubair

Please try my custom visuals
Anonymous
Not applicable

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?

Screenshot_4.png

Regards,
Ajay Emmadi

Anonymous
Not applicable

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

Anonymous
Not applicable

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

 
PYE_ACCT_997v2 = CALCULATE(MAX('Query1'[ACCT_997]), PREVIOUSYEAR('Dates'[Cycle Date]))
 
MChappy_0-1652463301565.png

 

 
 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.