cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
C_Jiang Regular Visitor
Regular Visitor

Calculate Sum according to the year value of the selected date

Hello,

 

Now in my report, I have a slicer DATE (zone 1),

and a histogram present the sum of sales of 2018 (CA PO N) and 2017 (CA PO N-1). (zone 2)

 

 

periode.PNG

 

C.A. NET H.T. PO Total pour N = CALCULATE([C.A. NET H.T. PO]; 'Date'[Annee] IN {2018})
C.A. NET H.T. PO Total pour N-1 = CALCULATE([C.A. NET H.T. PO];'Date'[Annee] in {2017})
 

 I want to change it to make the zone 2 always presents the sum of the same year as users' choices in slicer DATE.

 

For exemple:

 

If user chooses 17/08/2017 in the zone 1;

then zone 2 will show the sum of the whole year of 2017 and 2016.

 

If user chooses 17/08/2018 in the zone 1;

then zone 2 will show the sum of the whole year of 2018 and 2017.

 

I'll be very happy if someone could help me, thanks.

 

Chen_J

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Calculate Sum according to the year value of the selected date

hi, @C_Jiang

You could use formula like this to add the measure

Measure = CALCULATE([C.A. NET H.T. PO],FILTER(ALL('Date'), YEAR('Date'[Date])= YEAR(SELECTEDVALUE('Date'[Date]))))

But the result couldn't be assigned to every month, for date table has the relationship with data table, month has been filter if you only select one day, unless you use year-slicer or date table couldn't affect the data table.

I suggest you use a year-slicer, that will make the case very simple.

 

Best Regards,

Lin

 

 

 

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
9 REPLIES 9
Super User
Super User

Re: Calculate Sum according to the year value of the selected date

@C_Jiang try this:

 

C.A. NET H.T. PO Total pour N = 
VAR d = SELECTEDVALUES( Table[Date], TODAY() )
VAR y = YEAR( d )
RETURN
CALCULATE([C.A. NET H.T. PO]; 'Date'[Annee] = y)


C.A. NET H.T. PO Total pour N-1 = 
VAR d = SELECTEDVALUES( Table[Date], TODAY() )
VAR y = YEAR( d )-1
RETURN
CALCULATE([C.A. NET H.T. PO];'Date'[Annee] = y)

replace table[date] in this measure with your field





Did I answer your question? Mark my post as a solution!


Proud to be a Datanaut! Connect with me on Linkedin.
Feel free to email me with any of your BI needs.




 




BjoernSchaefer Regular Visitor
Regular Visitor

Re: Calculate Sum according to the year value of the selected date

Hello @C_Jiang,

 

i would suggest that you change your Date-Slicer to a Year-Slicer. Then, you can change the DAX to the following:

 

C.A. NET H.T. PO Total pour N = CALCULATE([C.A. NET H.T. PO]; Filter(DateTable; YEAR(Calendar'[Date])=SELECTEDVALUE([Year]))

 

C.A. NET H.T. PO Total pour N -1 = CALCULATE([C.A. NET H.T. PO]; Filter(DateTable; YEAR(Calendar'[Date])=SELECTEDVALUE([Year])-1)

 

This is a suggestion but i hope it helps.

 

Regards

 

BjoernSchaefer

C_Jiang Regular Visitor
Regular Visitor

Re: Calculate Sum according to the year value of the selected date

@parry2k Thank you very much,

 

I've tried these functions but I didn't get the result that I want.

The sum varies in different dates,  but what I want is : 

for one year,

no matter what date the user chooses,

the sum will always be one premenant value ( sum of this year).

 

1111.PNG

For exemple:

when we choose 12/01/2018 or 14/01/2018, the SUM should be the same, which is the SUM of the year 2018.

 

I don't know if my expression is clear enough.

Thanks again,

 

 

 

C_Jiang Regular Visitor
Regular Visitor

Re: Calculate Sum according to the year value of the selected date

Thanks @BjoernSchaefer ,

 

That's a good idea.

But now the slicer is working for another visul in the same report, and I wanted it works also for the histogram.

So I think I will not change it for now... I hope I could find a solution.

 

Super User
Super User

Re: Calculate Sum according to the year value of the selected date

@C_Jiang i was not sure how you are going to use the sum of the year, can you share what final output you want and that will change the dax.

 

based on what you showed, you want to see all the dates and you want total sum of the year for the date. 





Did I answer your question? Mark my post as a solution!


Proud to be a Datanaut! Connect with me on Linkedin.
Feel free to email me with any of your BI needs.




 




BjoernSchaefer Regular Visitor
Regular Visitor

Re: Calculate Sum according to the year value of the selected date

@C_Jiang,

 

then use YEAR(SELECTEDVALUE([date])) for the current year and YEAR(SELECTEDVALUE([date]))-1.

 

Regards

 

BjoernSchaefer

Community Support Team
Community Support Team

Re: Calculate Sum according to the year value of the selected date

hi, @C_Jiang

You could use formula like this to add the measure

Measure = CALCULATE([C.A. NET H.T. PO],FILTER(ALL('Date'), YEAR('Date'[Date])= YEAR(SELECTEDVALUE('Date'[Date]))))

But the result couldn't be assigned to every month, for date table has the relationship with data table, month has been filter if you only select one day, unless you use year-slicer or date table couldn't affect the data table.

I suggest you use a year-slicer, that will make the case very simple.

 

Best Regards,

Lin

 

 

 

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
C_Jiang Regular Visitor
Regular Visitor

Re: Calculate Sum according to the year value of the selected date

Thank you @v-lili6-msft great explanation.

 

 

C_Jiang Regular Visitor
Regular Visitor

Re: Calculate Sum according to the year value of the selected date

  @BjoernSchaefer I've tried the entire afternoon , in the end I changed the slicer. You are right right!!