cancel
Showing results for
Did you mean:
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)

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

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.

2 REPLIES 2
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))
``````

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.

Super User IV

@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.

Tutorial Series Dax Vs SQL Direct Query PBI Tips

Proud to be a Super User!

Announcements