Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Time Functions

Hi,

amitchandak Ashish_Mathur 

I have data Amount and Date in a table and wanted to calculate some time functions. I dont want to use any calendar table here. I derived Year and Month from the date field and using it for filters.

 

I do have filters like Year and Month. When I select Year 2022 and Month APRIL

 

1) Selected Month and Year: I just used SUM(Amount) -- I am getting correct results.

 

2) Same selected Month and Year but Last Year:

CALCULATE(SUM(Amount),SAMEPERIODLASTYEAR(Date))  --- not sure why this is not giving correct results. I suppose to get 2021 APRIL data but it is showing BLANK.
 
3) YTD: My financial year starts from APRIL to MARCH when I selected 2022 and APRIL. I suppose to see 2022 APRIL to 2023 March.  not sure how to get this done.
 
Last Year YTD: When I select 2022 APRIL then I supposed to see 2021 APRIL to 22 MARCH. not sure how to get this done.
 
Appreciated your support.
 
Thanks,
Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi Anonymous ,

     

    Please try:

    Last Year Same Month = 
CALCULATE(SUM('Table'[Amount]),FILTER(ALL('Table'),YEAR([Date])=SELECTEDVALUE('Table'[Date].[Year])-1 && MONTH([Date])=MAX('Table'[Date].[MonthNo])))
    FY = IF(MONTH(MAX('Table'[Date]))<4, YEAR(MAX('Table'[Date]))-1 , YEAR(MAX('Table'[Date])) )
    FY Sum = CALCULATE(SUM('Table'[Amount]),FILTER(ALL('Table'),[FY]=MAXX('Table',[FY]))) 
    Last Year YTD = CALCULATE(SUM('Table'[Amount]),FILTER(ALL('Table'),[FY]=MAXX('Table',[FY])-1))

     


    Best Regards,
    Eyelyn Qin
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi Anonymous ,

     

    Please try:

    Last Year Same Month = 
CALCULATE(SUM('Table'[Amount]),FILTER(ALL('Table'),YEAR([Date])=SELECTEDVALUE('Table'[Date].[Year])-1 && MONTH([Date])=MAX('Table'[Date].[MonthNo])))
    FY = IF(MONTH(MAX('Table'[Date]))<4, YEAR(MAX('Table'[Date]))-1 , YEAR(MAX('Table'[Date])) )
    FY Sum = CALCULATE(SUM('Table'[Amount]),FILTER(ALL('Table'),[FY]=MAXX('Table',[FY]))) 
    Last Year YTD = CALCULATE(SUM('Table'[Amount]),FILTER(ALL('Table'),[FY]=MAXX('Table',[FY])-1))

     


    Best Regards,
    Eyelyn Qin
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Yes it worked, much appreciated your support.