Dynamic measure date filter

[brinky]

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

[moizsherwani]

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]

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]

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]

Thanks Moiz,

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

[moizsherwani · Accepted solution]

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