Calculate Sum according to the year value of the selected date

[Anonymous]

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)

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

[v-lili6-msft · Accepted solution]

hi, @Anonymous

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

[Anonymous]

Thank you @v-lili6-msft great explanation.

[BjoernSchaefer]

Hello @Anonymous,

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

[Anonymous]

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.

[BjoernSchaefer]

@Anonymous,

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

Regards

BjoernSchaefer

[Anonymous]

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

[parry2k]

@Anonymous 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

[Anonymous]

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

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,

[parry2k]

@Anonymous 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.