Forum Discussion

brinky's avatar
brinky
Helper IV
4 years ago
Solved

Dynamic measure date filter

Hello,   Trying to get sales and total for 2020 to date (i.e 21/09/2021 current date) in this matrix   I'm stuck till here 😏   PY Intake = CALCULATE([Total], FILTER('Date', 'Date'[YearAct]=...
Need Help
  • moizsherwani's avatar
    moizsherwani
    4 years ago

    You need to have a "Day_Num" column in your date table which is simply DAY('Date'[Date])

     

    TotalSalesCY =
CALCULATE ( [TotalSales], FILTER ( 'Date', 'Date'[Year] = YEAR ( TODAY () ) ) )

TotalSalesLY =
CALCULATE (
    [TotalSales],
    FILTER (
        'Date',
        'Date'[Year]
            = YEAR ( TODAY () ) - 1
            && 'Date'[Month_Num] <= MONTH ( TODAY () )
            && 'Date'[Day_Num] <= DAY ( TODAY() )
    )
)

     

brinky's avatar
brinky
Helper IV
4 years ago

Thanks Moiz,

Neraly there 😄 total should be 2,155,503 as per table below

 

moizsherwani's avatar
moizsherwani
Continued Contributor
4 years ago

You need to have a "Day_Num" column in your date table which is simply DAY('Date'[Date])

 

TotalSalesCY =
CALCULATE ( [TotalSales], FILTER ( 'Date', 'Date'[Year] = YEAR ( TODAY () ) ) )

TotalSalesLY =
CALCULATE (
    [TotalSales],
    FILTER (
        'Date',
        'Date'[Year]
            = YEAR ( TODAY () ) - 1
            && 'Date'[Month_Num] <= MONTH ( TODAY () )
            && 'Date'[Day_Num] <= DAY ( TODAY() )
    )
)