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]=YEAR(TODAY())-1))

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() )
    )
)

     

5 Replies

  • moizsherwani's avatar
    moizsherwani
    Continued Contributor
    4 years ago

    Hi brinky ,

     

    See if this helps.

     

    1) The Year filter on the page is from the Date Table

     

    2) The bottom left table is just to show all the data that exists

     

    3) The bottom right table is the summarized view which shows the data for the same period last year

     

    There must be a relationship between the data table and the date table

     

     

    Here are the two measures

     

    TotalSales = SUM('Table'[Sale])

TotalSalesLY = CALCULATE([TotalSales],SAMEPERIODLASTYEAR('Date'[Date]))

     

    • 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 () )
    )
)