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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Homer
New Member

Calculate Works until i use FILTER

Hello Guys

i'm learning DAX and i was trying to make a measure for Sales Last year with Calculate and Filter

this formula works :

 

 

 

 

 

 

Sales LY =CALCULATE([Sales CY],SAMEPERIODLASTYEAR(Date[Date]))

 

 

 

 

 

Sales LY =CALCULATE([Sales CY],SAMEPERIODLASTYEAR(Date[Date]))

 

But when i add FILTER to the measure give me the values from the current year like "Sales CY"

 

 

 

 

 

Sales LY = CALCULATE([Sales CY], Filter (Date, SAMEPERIODLASTYEAR (Date[Date]) ))

 

 

 

 

 

 

 

  • I already have a date filter on the page relative date in this year

  • the invoices Table and Date table are joined on the date of the creation of the invoice (createat)

  • Data Model

Capture.PNG

 

also i don't get the weird shapes inside the blue circles 

Any Help will much appreciated 

5 REPLIES 5
v-shex-msft
Community Support
Community Support

HI @Homer,

Have you tried to use the date function to manually define filter range instead of time intelligence functions?

Time Intelligence "The Hard Way" (TITHW)  

Regards,
Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
amitchandak
Super User
Super User

@Homer , I hoping datedim is typo else

it need to like this ( I have not tested these)

 

Sales LY = CALCULATE([Sales CY], Filter (dim_date, dim_date[Date] in SAMEPERIODLASTYEAR (Date[Date]) ))

 

or

 

Sales LY = CALCULATE([Sales CY], Filter (Date, Date[Date] in SAMEPERIODLASTYEAR (Date[Date]) ))

Hey amit 

i fixed  the type and i tried your formula but it returned nothing ...

 

new Sales LY = CALCULATE([Sales CY], Filter ('Date','Date'[Date] in SAMEPERIODLASTYEAR('Date'[Date])))

 

@Homer , I checked on My data. This is not working.

The reason is that I can think of, is that the Filter tries to remove the data from the context. In the case of time intelligence, that is not needed.

 

Refer this http://dataap.org/blog/2019/04/22/difference-between-calculate-with-and-without-filter-expression/

i checked the article about the difference and that's why i wanted to use "FILTER" in my case ..

It should work but i don't have any ideas why it's not 

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

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