Filter Measure By Date

[Anonymous]

Hi,

I have looked at multiple forum postings and unfortunatly they don't quite answer my question. I need to create a really simple measure that filters before a particular date as oppose to usin a visual filter as another calculation need to work off this one.

I have created a calendar as per best practice advice given on the forums.

What I need to do is create a measure that filters SUM([Credit incl VAT]) before 01/06/2017 (dd/mm/yyyy)

I have created a relationship between Invoice Date and dd/mm/yyyy in the calendar table but this was not auto detected.

All I need is an expression (new to DAX) that will give me the filtered measure I am looking for?

Thanks,

Dominic

[v-danhe-msft · Accepted solution]

Hi @Anonymous,

Based on my test, you can refer to below steps:

1.I have entered some sample data to test your problem in below pictures.

2.Create a new measure to calculate your filter data.

FilterMeasure = CALCULATE (SUM('Integrated Calendar'[Credit]),FILTER (ALL('Integrated Calendar'[Invoice Data] ),'Integrated Calendar'[Invoice Data] <= DATE ( 2017, 1, 6 )))

3.Create a Card visual and add the [FilterMeasure] field and you can see the result.

You can also download the PBIX file to have a view.

https://www.dropbox.com/s/p91heavj2klrn4a/Filter%20Measure%20By%20Date.pbix?dl=0

Regards,

Daniel He

[Anonymous]

Hi Daniel @v-danhe-msft,

Thank you very much you have given me the formular which i needed to use, I had to change it slightly but it was pretty much perfect!

Kind regards

Dominic