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

HELP!! Power BI Date Filter Not Working

Hello, 

 

I could use some help, I have a Demand dataset that is a "snapshot" dataset with a "report date" used as the versioning mechanism so the same Demand_ID can be found in the dataset more than once but each instance is from a different reporting day.

 

I am trying to write measures that do a few things

 

1. Measure #1:  Counts the number of records in the data. I have done that with this Measure:

   Total Open Demand = COUNTA(DemandSnapshots[Demand_ID])    --- This seemed to work
image.png
2. Measure #2: Looks at the "Reporting Date" and returns the value of the "Earliest Date" for each month in that column
    CALCULATE((FIRSTDATE(DemandSnapshots[Report Date])),All(DemandSnapshots[Report Date]))
 
 - So for each "Month" the measure returns the "earliest report date" in that month. So in January if the report dates are 01/04 ,  01/09, 01/22, 01/31, the measure returns "01/04" as the "earliest" report date in January.
 
3. Measure #3:  Counts the Total Open demand (from Measure #1) BUT this time only returns the total count of demands Whose "Report Date" Equals the "Earlierst date" calculated in Measure #2. 
- This is the Measure that I am struggling with the Most because I cant seem to get it to work, when I run the below measure, it gives me the same "unfiltered" totals as in measure #1
 
This is the Measure I am writing:
Open Demand at start of each Month = CALCULATE([Total Open Demand],FILTER(DemandSnapshots,DemandSnapshots[Report Date]=[Earliest Date]))

image.png

When I run this measure I get the same unfiltered results as if the filtering out of "false" dates is not even happening.

 

If anyone can help me figure this one out, I would be super grateful!

Thanks!

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Resolver I
Resolver I

Re: HELP!! Power BI Date Filter Not Working

Instead of creating a measure to find the Earliest Date, try creating a calculated column instead.  Then, you can reference the Earliest Date column instead of a measure and that should do the trick:

 

Open Demand at start of each Month = CALCULATE([Total Open Demand],FILTER(DemandSnapshots,DemandSnapshots[Report Date]=DemandSnapshots[Earliest Date]))

 

Not the most efficient way of doing it if you've got a large dataset, but it should work

View solution in original post

2 REPLIES 2
Highlighted
Resolver I
Resolver I

Re: HELP!! Power BI Date Filter Not Working

Instead of creating a measure to find the Earliest Date, try creating a calculated column instead.  Then, you can reference the Earliest Date column instead of a measure and that should do the trick:

 

Open Demand at start of each Month = CALCULATE([Total Open Demand],FILTER(DemandSnapshots,DemandSnapshots[Report Date]=DemandSnapshots[Earliest Date]))

 

Not the most efficient way of doing it if you've got a large dataset, but it should work

View solution in original post

Highlighted
Super User I
Super User I

Re: HELP!! Power BI Date Filter Not Working

@J_263 

 

Can you try wrapping the "DemandSnapshots" in the filter expression with an ALL?

as in:

Open Demand at start of each Month = 
CALCULATE([Total Open Demand],
FILTER(ALL(DemandSnapshots),
DemandSnapshots[Report Date]=[Earliest Date]))

 

See it it works.





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






Helpful resources

Announcements
August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Upcoming Events

Upcoming Events

Wondering what events you could join or have an event to promote yourself? Check out our Upcoming Events.

Community Summit Australia – Join Online!

Community Summit Australia – Join Online!

Be a part of the leading Microsoft Business Applications digital event, curated for the APAC community.

Top Solution Authors
Top Kudoed Authors