How to make measure dynamic based on DATE selection via Date Slicer

[shanu_123]

"Today Sales= CALCULATE(COUNT(pbi_stock_sold[is_sold_from_stock]),FILTER(pbi_stock_sold,pbi_stock_sold[SoldDate]=TODAY()))" 

"Yesterday Sales  = CALCULATE(COUNT(pbi_stock_sold[is_sold_from_stock]),FILTER(pbi_stock_sold, pbi_stock_sold[SoldDate].[Date]=TODAY()-1))"

"This week Sales= CALCULATE(COUNT(pbi_stock_sold[is_sold_from_stock]),FILTER(pbi_stock_sold,YEAR(pbi_stock_sold[SoldDate])=YEAR(TODAY())&&WEEKNUM(pbi_stock_sold[SoldDate])=WEEKNUM(TODAY())))"

"This month Sales = CALCULATE(COUNT(pbi_stock_sold[is_sold_from_stock]),FILTER(pbi_stock_sold,YEAR(pbi_stock_sold[SoldDate])=YEAR(TODAY()) && MONTH(pbi_stock_sold[SoldDate])=MONTH(TODAY())))"

"This year Sales = CALCULATE(COUNT(pbi_stock_sold[is_sold_from_stock]),FILTER(pbi_stock_sold,YEAR(pbi_stock_sold[SoldDate])=YEAR(TODAY())))"

How can make above measure dynamic based on date selection in date slicer? They are static only show for recent date. Please guide....

[v-yulgu-msft · Accepted solution]

Hi @shanu_123,

First, you need a calendar table which lists continual unique date values. Don't need to create a relationship between this calendar table and 'pbi_stock_sold'.

CalendarTable=CALENDAR(DATE(2017,1,1),DATE(2018,12,31))

Add [date] column from 'CalendarTable' to slicer.

Modify original measures to replace TODAY() with MAX('CalendarTable'[date]), for example:

Yesterday Sales  = CALCULATE(COUNT(pbi_stock_sold[is_sold_from_stock]),FILTER(pbi_stock_sold, pbi_stock_sold[SoldDate].[Date]=MAX('CalendarTable'[date])-1))

Best regards,

Yuliana Gu

[Anonymous]

Hi,
I'm using this method to filter the values in my measures and it works perfectly, but when the measure is referenced in a table the table does not display the change made to the measure by the filter.
The measure used for the date:

Dia Variable = MAX('Tabla Calendario'[Date])

The calculated measure using the method to change the date:

M_ECO_6m= 

VAR DIA = [Dia Variable]

VAR MOTI = 0+CALCULATE(

    COUNT('SB'[ANTIGUEDAD]),

    'SB'[ANTIGUEDAD]IN { "+6M" },

    NOT('SB'[GC] IN {"GC"}),

    'SB'[date_op] = DIA,

    'SB'[Description] IN {"MO_ECO"}  
)
RETURN MOTI

The table im creating:

M_SB = {("M_Eco",[M_ECO_6m],[%_eco_6m],[M_ECO_X],[%_eco_x],[MO_ECO_G],[%_eco_g])}

when i put the measure in a KPI card it works fine but in the custom table it doesnt update with the filter.

Thanks in advance.