Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
J_263
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
Anonymous
Not applicable

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
PaulDBrown
Community Champion
Community Champion

@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.






Anonymous
Not applicable

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

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.