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 for your reply.

I'm tring to obtain last year sale to date without any filters or slicers, this is my code for this year's sales data.

 

CALCULATE([Total],
     FILTER('Date','Date'[YearAct]=YEAR(NOW())))

 

Now I would like to have for same for last year.

 

Thank you

 

moizsherwani's avatar
moizsherwani
Continued 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
    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() )
    )
)