HELP!! Power BI Date Filter Not Working

[J_263]

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

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]))

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!

[PaulDBrown]

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

[Anonymous · Accepted solution]

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