Last Year to Date calculation with Date Relative filter

[n3ptune]

for example i use date relative filter selection of Last 4 weeks 

there is a calculation of sales for these last 4 weeks and i want to calculate the same amount for last year same period which returns blank with all the usual ytd sameperiodlastyear etc time intelligence functions.

[n3ptune]

@v-kkf-msft now with that formula for running totals it start computing running total from first available date, how can we calculate based on filtered dates, 

so in the pic last column should be 
50
80

120

[v-kkf-msft]

Hi @n3ptune ,

Try the following formula:

Pervious Total = 
CALCULATE(
    [Previous],
    FILTER(
        ALLSELECTED('Table'[date]),
        'Table'[date] <= MAX('Table'[date])
    )
) 

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

Best Regards,
Winniz

[selimovd]

Hey @n3ptune ,

can you give more information?

A table, you measure and your desired result?

That would make it easier to find out what you want to archive.

Thank you and best regards

Denis

[n3ptune]

@selimovd 

1/1/20 - 100

2/1/20 - 200

15/2/20 - 40

28/2/20 - 50

20/3/20 - 30

26/3/20 - 20

1/1/21 - 200

2/1/21 - 100

15/2/21 - 50

28/2/21 - 90

20/3/21 - 80

26/3/21 - 90

if filter the relative date filter to Last 10 days the ytd result will be from 17/3/21 until today = 80+90=170

so i want a measure which will calculate the same for last year filter for last 10 days minus 1 year  so it will be from 17/3/20 till 26/3/20  30+20 = 50

[selimovd]

Hey @n3ptune ,

for that kind of calculations you need a date table for sure.

If you have a date table this could be your solution:

Last 10 days =
CALCULATE(
    SUM( MyTable[Amount] ),
    DATESINPERIOD( 'Date'[Date], MAX( 'Date'[Date] ), -10, DAY )
)

And then for the previous year:

Last 10 Days Previous Year = 
CALCULATE(
    [Last 10 days],
    SAMEPERIODLASTYEAR( 'Date'[Date] )
)

If you need any help please let me know.

If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards

Denis

Blog: WhatTheFact.bi

[selimovd]

Hey @n3ptune ,

did it work with my approach?

I'm curious if you could solve this issue 🙂

If you need any help please let me know.

If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards

Denis

Blog: WhatTheFact.bi

[n3ptune]

no it didnt , because when i am using date relative filter for example last 10 days last year's data is empty.

[v-kkf-msft · Accepted solution]

Hi @n3ptune ，

Try the following formula:

Last days = SUMX(ALLSELECTED('Table'),'Table'[value])

Previous = 
CALCULATE(
    SUM('Table'[value]),
    ALL('Table'[date]),
    SAMEPERIODLASTYEAR('Table'[date])
)

Pervious Total = SUMX(ALLSELECTED('Table'[date]),[Previous])

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

Best Regards,
Winniz

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

[n3ptune]

Can you also make the last days and previous year total as running total ?

[n3ptune]

@v-kkf-msft 

i think i did this

Previous Total = CALCULATE(SUMX(ALLSELECTED('Table'[date]),[Previous]),FILTER(ALL('Table'),'Table'[date]<=MAX(('Table'[date])))) this all instead allselected for last years running total made the difference.
for this year it works as usual date<= max date