SAMEPERIODLASTYEAR - Total Problem and Partial Solution

[Cmuglioni]

Hi Everyone,

Let me explain my issue: I am looking to have the amount of wine cases (#Cajas) of the same period last year. Of course I used the following Dax Sentence and it works fine with details:

Cajas N-1 = CALCULATE([Cajas],SAMEPERIODLASTYEAR(Calendario[Fecha]))

But when I put this in a matrix, the total amount doesn't match with the details:

I should have 17.351 as a total.

Observation: I have a Date Table (Calendario[Fecha] in my case) that refers to the date from 01-01-2020 to Today()-1 (my data is always actualized as of yesterday) and it is connected to my fact table.

I figured to create a new measure that shows me the correct result:

Cajas N-1'=CALCULATE(SUM(Inventario[Cajas]),DATESBETWEEN(Calendario[Fecha],Date(Max(Calendario[Año])-1,FORMAT(MIN(Calendario[Fecha]),"mm"),FORMAT(MIN(Calendario[Fecha]),"dd")),EDATE(MAX(Calendario[Fecha]),-12))).

I would like to understand why it doesn't work in the first place and if there is a better way to do so! I think that my mistake come from the fact that my visual is not filtering by dates (fecha in this case) because it is refering to Today()-1.

Thanks in advance!!

[Cmuglioni]

Hi ValtteriN,

I tried with different functions and I have exactly the same results as above 😕 :

CALCULATE(SUMX(Inventario,Inventario[Cajas]),SAMEPERIODLASTYEAR('Calendario'[Fecha]))

CALCULATE(SUMX(Inventario,Inventario[Cajas]),SAMEPERIODLASTYEAR('Calendario'[Fecha]),ALLSELECTED(Calendario[Fecha]))

CALCULATE(SUMX(KEEPFILTERS(VALUES(Calendario[Fecha])),[Cajas]),SAMEPERIODLASTYEAR(Calendario[Fecha]))

CALCULATE([Cajas],SAMEPERIODLASTYEAR(Calendario[Fecha]),ALLSELECTED(Calendario[Fecha]))

I will try to investigate a little more in this, thx

[amitchandak · Accepted solution]

@Cmuglioni , Power bi recalculate, grand total. As there is no date selected. It is 1 year behind the measure of the duration available. I think it is summing up everything before 1 year from the end date of the calendar in the grand total. The other one forces a range. so it is working in that case

[Cmuglioni]

Hi @amitchandak, indeed, I think my issue is coming from the fact that there is no date selected. My Calendar Table has a daily update and everytime I update my report I don't have a selected value because I am considering the all universe of dates available. Is there a way to automatically select the last date in order to make the time functions work? 

[ValtteriN]

Hi,

The total in table visual works in somewhat counter-intuituve way. I recommend checking this post by SQLBI to understand what is going on: https://www.sqlbi.com/articles/summing-values-for-the-total/

My first instinct to improve your dax would be to use SUMX or perhaps do something with ALLSELECTED. 
So perhaps something like: CALCULATE([Cajas],SAMEPERIODLASTYEAR(Calendario[Fecha]),ALLSELECTED(Calendario[Fecha]))