Forum Discussion

nsadams87xx's avatar
nsadams87xx
Icon for Helper III rankHelper III
8 years ago

Date Hierarchy Displaying too much data

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...
Need Help
nsadams87xx's avatar
nsadams87xx
Icon for Helper III rankHelper III
8 years ago

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.

Ashish_Mathur's avatar
Ashish_Mathur
Icon for Super User rankSuper User
8 years ago

Hi,

 

Try this

 

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

  • nsadams87xx's avatar
    nsadams87xx
    Icon for Helper III rankHelper III
    8 years ago

    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.