Forum Discussion
Query regarding Date Hierarchy
Hello All,
I have a query. Pls help me out anyone.
Whenever I am loading Year, Month (which are calculated columns) fields from Calendar table I am getting data exactly from the date/month from which data is available in power bi. Pls find below screenshot where the minimum date is 2nd dec 2023.
But when I am using Year, Month as Date Hierarchy it shows me those dates as well which are not available in power bi. Pls see below screenshot.
Can anyone pls help me with the logic thats running behind this? I am getting confused.
Thanking you guys in advance for your help.
10 Replies
Is there a particular reason why you would like to use the Hierarchy over the Values (option 1 vs. option 2)?
No reason but I am just curious why they are performing differently. I wanna know why this is occuring ExcelMonke
Basically I am trying to replace blank with 0 so I am using this dax
Campaign Values_New =IF(FIRSTNONBLANK('Param'[Attributes],1)= "Total Adv Sales",SUMX(campaign_report,campaign_report[directRevenue]+campaign_report[indirectRevenue])+0)When I am not using +0 the visual is working perfectly under hierarchy level but when i try to use +0 the hierarchy shows all the months which are not even in power bi.
Yes, that makes sense because it will assign the "+0" to all dates. One way you can overcome this is to apply a slicer so that it only shows the desired date range.
An alternative way would be to add Time Intelligence functions to your measure - although this is a bit more complex than a simple slicer.I have two questions
1. Why will it assign +0 to all dates? Can u explain me the functionality pls.2. Adding time intellignece function? Can u just show me a syntax as to how to achieve it in this dax
Campaign Values_New =IF(FIRSTNONBLANK('Param'[Attributes],1)= "Total Adv Sales",SUMX(campaign_report,campaign_report[directRevenue]+campaign_report[indirectRevenue])+0)Sure!
- Based on your explanation and examples you can think of it as the following. Imagine you have the table where you don't change the blank to zero. It may look like this:
Jan and Feb have no values (they are blank/empty), so PowerBI will ignore them. However, once you change the blank to "0", it treats 0 as a "distinct value", rather than a blank. i.e. it is no longer empty. Does this make sense?Year Month Value 2022 Jan 2022 Feb 2022 Mar 123 2022 Apr 456 - There are many to pick from, and I would need to know what your desired outcome is. I'd recommend checking out this page to see which one works best for your use-case:
https://learn.microsoft.com/en-us/dax/time-intelligence-functions-dax
- Based on your explanation and examples you can think of it as the following. Imagine you have the table where you don't change the blank to zero. It may look like this:
