Forum Discussion

brinky's avatar
brinky
Icon for Helper IV rankHelper 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() )
    )
)

     

moizsherwani's avatar
moizsherwani
Icon for Continued Contributor rankContinued Contributor
4 years ago

There are plenty of way to do this (some possibly better) but this should get you the result.

 

Thanks,

 

Moiz

If this post helps, please "Accept" it as Solution to help other members find it.

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

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

 

 

 

brinky's avatar
brinky
Icon for Helper IV rankHelper IV
4 years ago

Thanks Moiz,

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

 

  • moizsherwani's avatar
    moizsherwani
    Icon for Continued Contributor rankContinued 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() )
    )
)