Filtering data by the last day of the selected month

[MGASP01]

Hi all! I'm new to power bi and dax so bear with me..

I have a table named ocr_mc with 3 columns:

• GIORNO (this is a date column)
• OCR7_RESOLUTIVE
• OCR7_TOTAL

I have this measure I created:

OCR7_LAST_DAY = CALCULATE((SUM(ocr_mc[OCR7_RESOLUTIVE])/sum(ocr_mc[OCR7_TOTAL])*100), FILTER(ocr_mc,ocr_mc[giorno] = lastdate(ocr_mc[giorno])))

I have a slicer that filters months, so when I select February, lastdate(ocr_mc[giorno]) returns 29/02 and when I select March, it returns 25/03.

The problems is the the measure OCR7_LAST_DAY seem not to filter the last day of the month, but it returns data for the whole month.

Can anyone help me on this?

[amitchandak]

Not sure I got it but you can use EOmonth

OCR7_LAST_DAY = CALCULATE((SUM(ocr_mc[OCR7_RESOLUTIVE])/sum(ocr_mc[OCR7_TOTAL])*100), FILTER(ocr_mc,ocr_mc[giorno] = eomonth(ocr_mc[giorno],0)))

[MGASP01]

Hi amitchandak

Thanks for answering me back.

Using EOmonth seems to work fine with previous months, but not with the current month.

It returns no data since today it is not the end of the month.

So where is the trick? Why does eomonth work and lastdate don't?

I'm missing some basics here..

[amitchandak]

Is your calendar ending today?

generated like

Date = calendar(Date(2019,01,01), Today())

Because when Try

EOMonth = EOMONTH(TODAY(),0)

This means no use date column in a table. It gives me 3/31

[MGASP01]

My calendar ends on March 31, since I generated it using calendarauto(3) (fiscal year ends on March here in Italy).

Anyway, I assume it is correct that I don't get any result for March 31 since I don't have data for that day.

It's just that I don't understand why I can't filter data using LASTDATE to get the result for the last day with data in my table.

Why do I get the result for the whole month instead?

[amitchandak]

If you are using on transaction table it should return 25th. As it takes row context I doubt in a formula with sales table, the date might also return 25th. You can text. By doing some play around 

https://docs.microsoft.com/en-us/dax/lastdate-function-dax

If you need more help mark me @

Appreciate your Kudos.

[MGASP01 · Accepted solution]

@amitchandak using MAX function seems to work fine both for previous months and the current one:

FILTER(ocr_mc,ocr_mc[GIORNO] = MAX(ocr_mc[GIORNO]))

Thank you for helping me out!