Forum Discussion

Fidzi8's avatar
Fidzi8
Icon for Helper V rankHelper V
5 years ago
Solved

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

Need Help
Tips and Tricks
  • v-zhenbw-msft's avatar
    v-zhenbw-msft
    5 years ago

    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

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    5 years ago

    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.

  • v-zhenbw-msft's avatar
    v-zhenbw-msft
    Icon for Community Support rankCommunity Support
    5 years ago

    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.