Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
brinky
Helper IV
Helper IV

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

Screenshot 2021-09-22 002740.png

1 ACCEPTED SOLUTION
moizsherwani
Continued Contributor
Continued Contributor

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

 

Thanks,

Moiz
Was I able to answer your question? Mark my post as a solution to help others. Kudos if you liked the solution.

View solution in original post

5 REPLIES 5
moizsherwani
Continued Contributor
Continued Contributor

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

 

moizsherwani_0-1632265483908.png

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

 

moizsherwani_1-1632265504487.png

 

Here are the two measures

 

TotalSales = SUM('Table'[Sale])

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

 

Thanks,

Moiz
Was I able to answer your question? Mark my post as a solution to help others. Kudos if you liked the solution.

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
Continued Contributor
Continued Contributor

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

 

 

 

Thanks,

Moiz
Was I able to answer your question? Mark my post as a solution to help others. Kudos if you liked the solution.

Thanks Moiz,

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

 

Screenshot 2021-09-22 061942.png

moizsherwani
Continued Contributor
Continued Contributor

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

 

Thanks,

Moiz
Was I able to answer your question? Mark my post as a solution to help others. Kudos if you liked the solution.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.