KPI with current week sales vs previous week not working

[ezepbi]

Hi, I've searched for my issue but I couldn't find the solution I need.
My problem is that I'm trying to create a KPI to show current week sales vs prev week and the same with months but it isn't working.
it shows me (blank) for current sales and current sales in prev sales.



Here is the dax formula for each measure.

Total de Ventas = SUM(VENTAS[Venta Total])

Ventas de la Semana Pasada =

CALCULATE(

    [Total de Ventas],

    DATEADD(

        'Calendario'[Date],

        -7,

        DAY

    )

)

Ventas del Mes Pasado =

CALCULATE(

    [Total de Ventas],

    DATEADD(

        'Calendario'[Date],

        -1,

        MONTH

    )

)

and here is the dax formula for my calendar table

Calendario =

var _fromYear=2024

var _toYear=2024

var _startOfFiscalYear=4

//**************

var _hoy=TODAY()

return

ADDCOLUMNS(

    CALENDAR(

                DATE(_fromYear,1,1),

                DATE(_toYear,12,31)

),

"Año",YEAR([Date]),

"Comienzo del Año",DATE( YEAR([Date]),1,1),

"Fin del Año",DATE( YEAR([Date]),12,31),

"Mes",MONTH([Date]),

"Comienzo del Mes",DATE( YEAR([Date]), MONTH([Date]), 1),

"Fin del Mes",EOMONTH([Date],0),

"Días en el Mes",DATEDIFF(DATE( YEAR([Date]), MONTH([Date]), 1),EOMONTH([Date],0),DAY)+1,

"Año Mes Número",INT(FORMAT([Date],"YYYYMM")),

"Año Mes Nombre",FORMAT([Date],"YYYY-MMM"),

"Día",DAY([Date]),

"Nombre de Día",FORMAT([Date],"DDDD"),

"Nombre de Día corto",FORMAT([Date],"DDD"),

"Día de la semana",WEEKDAY([Date]),

"Día del año",DATEDIFF(DATE( YEAR([Date]), 1, 1),[Date],DAY)+1,

"Nombre del Mes",FORMAT([Date],"MMMM"),

"Nombre del Mes corto",FORMAT([Date],"MMM"),

"Trimestre",QUARTER([Date]),

"Nombre del trimestre","Q"&FORMAT([Date],"Q"),

"Año/trimestre Número",INT(FORMAT([Date],"YYYYQ")),

"Año/trimestre Nombre",FORMAT([Date],"YYYY")&" Q"&FORMAT([Date],"Q"),

"Comienzo del trimestre",DATE( YEAR([Date]), (QUARTER([Date])*3)-2, 1),

"Fin del trimestre",EOMONTH(DATE( YEAR([Date]), QUARTER([Date])*3, 1),0),

"Semana del Año",WEEKNUM([Date]),

"Comienzo de la semana", [Date]-WEEKDAY([Date],2)+1,

"Fin de la semana",[Date]+7-WEEKDAY([Date]),

"Año Fiscal",if(_startOfFiscalYear=1,YEAR([Date]),YEAR([Date])+ QUOTIENT(MONTH([Date])+ (13-_startOfFiscalYear),13)),

"Trimestre fiscal",QUARTER( DATE( YEAR([Date]),MOD( MONTH([Date])+ (13-_startOfFiscalYear) -1 ,12) +1,1) ),

"Mes fiscal",MOD( MONTH([Date])+ (13-_startOfFiscalYear) -1 ,12) +1,

"Día Offset",DATEDIFF(_hoy,[Date],DAY),

"Mes Offset",DATEDIFF(_hoy,[Date],MONTH),

"Trimestre Offset",DATEDIFF(_hoy,[Date],QUARTER),

"Año Offset",DATEDIFF(_hoy,[Date],YEAR)

)

as you can see the column is correct (i think) "Comienzo de la semana", [Date]-WEEKDAY([Date],2)+1,


I'm putting the measure "Total de Ventas" in the value box, "Comienzo de la semana" in the tendency box and "Ventas de la semana pasada" in the destiny box.

[Ritaf1983 · Accepted solution]

Hi @ezepbi 

The visualization doesn't work because it doesn't apply any filtering based on the measures you've placed on it.

Unlike tables or other charts, cards don't have a "date" column that provides context.

Therefore, for it to work, the card must be filtered either by a slicer if it's dynamic or simply by a related filter if it always needs to be "last by report day".

For example, here's how it would look when comparing months:

For the weeks you should use the same logic on week granularity.

The pbix with the example is attached.

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

[ezepbi]

you're awesome thanks