Forum Discussion

Cmuglioni's avatar
Cmuglioni
Frequent Visitor
4 years ago
Solved

SAMEPERIODLASTYEAR - Total Problem and Partial Solution

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

 
  • amitchandak's avatar
    amitchandak
    4 years ago

    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

4 Replies

  • ValtteriN's avatar
    ValtteriN
    Icon for Community Champion rankCommunity Champion
    4 years ago

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

  • Cmuglioni's avatar
    Cmuglioni
    Frequent Visitor
    4 years ago

    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's avatar
      amitchandak
      Icon for Super User rankSuper User
      4 years ago

      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's avatar
        Cmuglioni
        Frequent Visitor
        4 years ago

        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?