Date Hierarchy Displaying too much data

[nsadams87xx]

Hey Everyone,

I have a matrix that is showing a date hierarchy for previous budget vs actual data.  When I drill down to a month level, the matrix also shows months that haven't happened yet.  Is there a way to prevent that?  Or since it's a hierarchy, does Power BI have to take into account every month within the higher structure levels (year, quarter)?

[Phil_Seamark]

Hi @nsadams87xx

You could add report, page or visual level filters to filter out the dates you don't want.  Or add a slicer to your page.  Finally you can add DAX code to your measures to only return data for relevant date ranges. 

[nsadams87xx]

Thanks Phil.  I'm curious as to why the matrix goes into the future.  My data set dates in all of my tables stop 14 days prior to the current date.  So for today, I would have data up to Feb 5, 2018 (which is correct, our system that feeds the data in SQL has a lag time).  Shouldn't the March 2018 and beyond just not display at all?

[Phil_Seamark]

The date table being used on the axis probably goes to the end of the year.   Do you have your own Date table or are you using the in-built auto date tables provided?

[nsadams87xx]

If I think I know how to answer this, I'm using my own table with dates in it.  I'm using the built-in hierarchy capability on the axis.

[parry2k]

% column in your report seems to be a calculated measure and wondering if that is what makig it show data in the future?

Two things:

1. Can you remove % column and check if it doesn't shows blank rows?

2. if you remove % column and you are NOT seeing blank rows, share the measure you used for %

[nsadams87xx]

Removing that column is showing the matrix correctly now.  What if I aggregated my data by a month level rather than at a date level?

[nsadams87xx]

Sorry forgot the calculation.

% Change from Budget = IF(

(SUM(em_encounter_fact_date_agg[Pt Volume])/ sum(em_encounter_fact_date_agg[adj_arrivals_budget]) - 1) = -1 && ([Pt Volume Change %] = 0) && [Staff Hours % Change] = 0, 0, SUM(em_encounter_fact_date_agg[Pt Volume])/ sum(em_encounter_fact_date_agg[adj_arrivals_budget]) - 1

)

I have the IF because without it, the future months were showing as -100.00% and causing havoc with the Percentage Total.

[parry2k]

% Change from Budget = IF(

(SUM(em_encounter_fact_date_agg[Pt Volume])/ sum(em_encounter_fact_date_agg[adj_arrivals_budget]) - 1) = -1 && ([Pt Volume Change %] = 0) && [Staff Hours % Change] = 0, BLANK(), SUM(em_encounter_fact_date_agg[Pt Volume])/ sum(em_encounter_fact_date_agg[adj_arrivals_budget]) - 1

)

Try with this change, basically you are saying if you don't have any value then % will be blank. I assumed you assigned value to 0 to not to show -100%

[Ashish_Mathur]

Hi,

Try this

=IF(ISBLANK(SUM(em_encounter_fact_date_agg[Pt Volume])),BLANK(),your_formula_here)

[nsadams87xx]

Unfortunately I got the same results.  The future months are still showing up.  I'm pretty convinced it's because of the tier structure of the dates.  My thinking is since year is being included in my hierarchy it's forcing itself to have to take the entire year into account including dragging the lower tiers with it when making the calculations with my measurement.  I think I'm going to put this one to bed for now.  Thank you everyone.

[parry2k]

As mentined if you share your % calculation, it will help to understand what is going on and find out the solution. Thanks!