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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
nsadams87xx
Helper III
Helper III

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 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)?Heirarchy.JPG

11 REPLIES 11
Phil_Seamark
Employee
Employee

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. 


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

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?

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?


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

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.

 

Date Table.JPGDate Hierarchy.JPG

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



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

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?

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.

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



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Hi,

 

Try this

 

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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.

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



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

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