cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
DomWest Regular Visitor
Regular Visitor

Filter Measure By Date

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.

 

PowerBI Calendar.PNG

 

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

 

PowerBI Measure.PNG

 

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

1 ACCEPTED SOLUTION

Accepted Solutions
Microsoft v-danhe-msft
Microsoft

Re: Filter Measure By Date

Hi @DomWest,

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

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

A.PNG

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.

B.PNG

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

Community Support Team _ Daniel He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

2 REPLIES 2
Microsoft v-danhe-msft
Microsoft

Re: Filter Measure By Date

Hi @DomWest,

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

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

A.PNG

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.

B.PNG

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

Community Support Team _ Daniel He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

DomWest Regular Visitor
Regular Visitor

Re: Filter Measure By Date

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

Helpful resources

Announcements
New Kudos Received Badges Coming

New Kudos Received Badges Coming

Kudos to you if you earned one of these! Check your inbox for a notification.

Microsoft Implementation for Communities Wins Award

Microsoft Implementation for Communities Wins Award

Learn about the award-winning innovation that was implemented across Microsoft’s Business Applications Communities.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Kudoed Authors (Last 30 Days)
Users online (3,106)