Forum Discussion

boby's avatar
boby
Regular Visitor
9 years ago
Solved

Compare sameperiodlastyear, from date to date

Hi all,   eg : total amount = CALCULATE(sum(TABLE1[amount]); LASTDATE(TABLE1[Date]))  = 23 Total amount sameperiodlastyear =  CALCULATE([total amount ]; SAMEPERIODLASTYEAR(TABLE1[Date]))  = 31   ...
Need Help
  • Vvelarde's avatar
    Vvelarde
    9 years ago

    boby

     

    Maybe exist a short version but i'll go to a meeting and don't have time to optimize

     

    MTD =
CALCULATE (
    SUM ( Table1[Amount] ),
    FILTER ( DATESMTD ( Calendario[Date] ), Calendario[Date] <= TODAY () )
)
    M2 MTD LY =
CALCULATE (
    [MTD],
    FILTER (
        DATEADD ( Calendario[Date], -1; YEAR ),
        Calendario[Date]
            <= DATE ( YEAR ( TODAY () ) - 1, MONTH ( TODAY () ), DAY ( TODAY () ) )
    )
)
boby's avatar
boby
Regular Visitor
9 years ago

I have a date table to connect with my data table. but i'm doing something wrong !!

 

M0 = CALCULATE(sum(Test[Amount]); DimDate[year] = 2015) the mesure is 31

M1 = CALCULATE(sum(Test[Amount]); DimDate[year] = 2016) the mesure is 23

MTD = CALCULATE(sum(Test[Amount]) ; DATESMTD(DimDate[Date]))  nothing why ?

M2 MTD LY = CALCULATE([MTD] ; DATEADD(DimDate[Date]; -1; YEAR)) nothing why ?

 

what i want is :

Result of current month : 23 (from 1/12/2016 to 23/2016

Result of last year : 23  : (from 1/12/2015 to 23/12/2015

 

Thank you

Vvelarde's avatar
Vvelarde
Icon for Community Champion rankCommunity Champion
9 years ago

boby

 

Maybe exist a short version but i'll go to a meeting and don't have time to optimize

 

MTD =
CALCULATE (
    SUM ( Table1[Amount] ),
    FILTER ( DATESMTD ( Calendario[Date] ), Calendario[Date] <= TODAY () )
)
M2 MTD LY =
CALCULATE (
    [MTD],
    FILTER (
        DATEADD ( Calendario[Date], -1; YEAR ),
        Calendario[Date]
            <= DATE ( YEAR ( TODAY () ) - 1, MONTH ( TODAY () ), DAY ( TODAY () ) )
    )
)
  • boby's avatar
    boby
    Regular Visitor
    9 years ago


    Awesome, works perfectly.

    I need to analyse this long formula after vacation.

    Thank you

    and Merry Christmas and a happy New year

    • v-huizhn-msft's avatar
      v-huizhn-msft
      Icon for Microsoft Employee rankMicrosoft Employee
      9 years ago

      Hi boby,

      I very gald the formula work perfectly, please mark the corresponding reply as solution, which will help more people.

      Best Regards,
      Angelia

  • acrmorris's avatar
    acrmorris
    Frequent Visitor
    9 years ago

    Vvelarde, there may be a shorter solution, but this worked for me first time and because of the format means I can understand what is happening in the formula. Maybe I will take a look at shortening it myself when I have a bit more experience with Power BI.

     

    Thanks for the post