Forum Discussion

J_263's avatar
J_263
Regular Visitor
6 years ago
Solved

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
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!

 

Data Protection
  • Anonymous's avatar
    Anonymous
    6 years ago

    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

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    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

  • PaulDBrown's avatar
    PaulDBrown
    Icon for Community Champion rankCommunity Champion
    6 years ago

    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.