Compare last 7 days(CURRENT YEAR) with same period (LAST YEAR)

[Anonymous]

Hi, I would like to create a card (or use KPI visual as on the photo ) to display sales last 7 days and compare with the same period last year. I have calculated the sales for the last 7 days but I dont know how to calculate the same thing for the last year and  compare the metrics on a card.

The goal   should be actually a KPI visual that accepts a measure in Goal field , a measure that calculates the sales same period last year, or maybe any idea of a different visual that i can use to compare the metrics on a card.

Any help appreciated.

Thank you.

[Anonymous]

Last 7 days LY =

VAR _T = today()

VAR _7D= TODAY()-7

VAR _LYT=DATE(YEAR(_T)-1,MONTH(_T), DAY(_T))

VAR _LY7D=_LYT-7

VAR _BN=CALCULATE([measure],USERELATIONSHIP('Date Table'[Date],FT[date2]))

RETURN

CALCULATE ( _BN,FILTER ( ALL ( 'Date Table'), 'Date Table'[Date] <= _LYT && 'Date Table'[Date] > _LY7D )

)

I have modified like this, but I dont know why this doesn not work

[VahidDM]

Hi @Anonymous 

It'd be great if you have a calendar table (Date Table) in your model=

Try this measure to find last year same period:

If you don't have a Calendar Table:

Last Year 7Days =
VAR _T =
    TODAY ()
VAR _7D =
    TODAY () - 7
VAR _LYT =
    DATE ( YEAR ( _T ) - 1, MONTH ( _T ), DAY ( _T ) )
VAR _LY7D = _LYT - 7
RETURN
    CALCULATE (
        SUM ( table[colum] ),
        FILTER ( ALL ( table ), table[date] <= _LYT && table[date] > _LY7D )
    )

If you have a Calendar Table:

Last Year 7Days =
VAR _T =
    TODAY ()
VAR _7D =
    TODAY () - 7
VAR _LYT =
    DATE ( YEAR ( _T ) - 1, MONTH ( _T ), DAY ( _T ) )
VAR _LY7D = _LYT - 7
RETURN
    CALCULATE (
        SUM ( table[colum] ),
        FILTER ( ALL ( Date), Date[Date] <= _LYT && Date[Date] > _LY7D )
    )

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
LinkedIn: www.linkedin.com/in/vahid-dm/

[Anonymous]

Hi Vahid,

Thank you for the help. Yes,  I have a Calendar Table but the date column for which a would like to make the calculations in an inactive relationship with the Calendar table.  SO can you show me how to modify the measure using USERALATIONSHIP ?

[Anonymous]

One more thing, when using this measure in a card visual, I try to filter the card by customer (column from the same FT,)the card doesnt change according to the selection.