Query regarding Date Hierarchy

[Anonymous]

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.

[ExcelMonke]

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. 

[Anonymous]

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)

[ExcelMonke]

Sure!

1. 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:

   Year	Month	Value
   2022	Jan
   2022	Feb
   2022	Mar	123
   2022	Apr	456

   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?
2. 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 

[Anonymous]

Thx for your help but I'm still confused. 

[Anonymous]

Hi @Anonymous ,

Can you please tell me what else you are confused about?

For your first doubt, I think that even if you don't add 0, you can add index columns when you encounter duplicate values and it can help you avoid aggregation.

Best Regards

Yilong Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi @Anonymous ,

Thanku for your response. Should I just add an index column and not add +0, Will that work perfectly?

[Anonymous]

Thanku for your explanation. I still have queries. Can u pls check your chatbox or mail as I have send u a text.

[ExcelMonke]

Is there a particular reason why you would like to use the Hierarchy over the Values (option 1 vs. option 2)?

[Anonymous]

No reason but I am just curious why they are performing differently. I wanna know why this is occuring @ExcelMonke

[Anonymous]

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.