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
Fidzi8
Helper IV
Helper IV

Last month value filter

Hi,

I don´t know how create DAX formula: when I want to see last value ( not sum value) 

 

2020-10-23_13h49_41.png

When I filter 01.09.2020 - 01.10.2020  I want to see 13.   Now i see 27 - I see sum of solumn Number.

 

Thank you 
Ondřej

1 ACCEPTED SOLUTION
v-zhenbw-msft
Community Support
Community Support

Hi @Fidzi8 ,

 

We can create a measure to meet your requirement.

 

Measure = 
var _maxdate = CALCULATE(MAX('Table'[Date]),ALLSELECTED('Table'))
return
CALCULATE(SUM('Table'[Number]),FILTER('Table','Table'[Date]=_maxdate))

 

last1.jpg

 

If it doesn’t meet your requirement, could you please show the exact expected result based on the table that you have shared?

 

Best regards,

 

Community Support Team _ zhenbw

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

BTW, pbix as attached.

View solution in original post

2 REPLIES 2
v-zhenbw-msft
Community Support
Community Support

Hi @Fidzi8 ,

 

We can create a measure to meet your requirement.

 

Measure = 
var _maxdate = CALCULATE(MAX('Table'[Date]),ALLSELECTED('Table'))
return
CALCULATE(SUM('Table'[Number]),FILTER('Table','Table'[Date]=_maxdate))

 

last1.jpg

 

If it doesn’t meet your requirement, could you please show the exact expected result based on the table that you have shared?

 

Best regards,

 

Community Support Team _ zhenbw

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

BTW, pbix as attached.

amitchandak
Super User
Super User

@Fidzi8 , With time intelligence and date table, try formula like

 

MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))

diff = [MTD Sales]-[last MTD Sales]
diff % = divide([MTD Sales]-[last MTD Sales],[last MTD Sales])

 

Use date from date table as filter

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.

 

Please provide your feedback comments and advice for new videos
Tutorial Series Dax Vs SQL Direct Query PBI Tips
Appreciate your Kudos.

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.