Time functions

[Anonymous]

Hi,

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.

Date	Amount
01/01/2021	500
01/02/2021	573
01/03/2021	4723
01/04/2021	473
01/05/2021	57
01/06/2021	869
01/07/2021	472
01/08/2021	887
01/09/2021	739
01/10/2021	273
01/11/2021	2087
01/12/2021	738
01/01/2022	830
01/02/2022	245
01/03/2022	3267
01/04/2022	734
01/05/2022	265
01/06/2022	257
01/07/2022	5489
01/08/2022	378
01/09/2022	5478
01/10/2022	3267
01/11/2022	439
01/12/2022	3278
01/01/2023	3289
01/02/2023	389
01/03/2023	4590

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,

[Anonymous · Accepted solution]

Hi @Anonymous ,

Please refer to my pbix file to see if it helps you.

Create a measure.

Same selected Month and Year but Last Year =
VAR _date =
    SELECTEDVALUE ( 'Table'[Date] )
VAR _1yearbefore =
    EDATE ( _date, -12 )
RETURN
    CALCULATE (
        SUM ( 'Table'[Amount] ),
        FILTER ( ALL ( 'Table' ), 'Table'[Date] = _1yearbefore )
    )

Create a column and a measure about YTD.

SortCol_COLUMN =
VAR _year =
    YEAR ( 'Table'[Date] )
VAR _month =
    MONTH ( 'Table'[Date] )
VAR _RESULT =
    CONCATENATE (
        IF ( [Date].[MonthNo] > 3, _year, _year - 1 ),
        FORMAT ( IF ( _month > 3, _month - 3, _month + 9 ), "00" )
    )
RETURN
    LEFT ( _RESULT, 4 )

YTD =
CALCULATE (
    SUM ( 'Table'[Amount] ),
    FILTER (
        ALL ( 'Table' ),
        'Table'[SortCol_COLUMN] = SELECTEDVALUE ( 'Table'[SortCol_COLUMN] )
    )
)

Best Regards

Community Support Team _ Polly

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