Is there a way to check with date dimension is filtered on a chart : by day, by month, by year?

[extri13]

Dear BI users,

Is there a way to check with date dimension is filtered on a chart : by day, by month, by year?

This is for switching DoD & MoM on a chart by X-axis date dimension.

My purpose and the findings of details so far listed below.

Purpose

• Show the MoM and DoD data 
• Hope to show both MoM and DoD on one chart to better experience
• X-axis could be changed by Drill Up/Down or Slicer
• (Check below captured charts)

Findings

• Made measures for DoD and MoM helped by (http://community.powerbi.com/t5/Community-Blog/Monitor-your-Website-Growth-with-one-click-with-Googl...)
• Available to check the filter activaction on one column by "ISFILTERED" DAX function
• However, the [dates] column is mix of day/month/year and "ISFILTERED" can not be adapted

Chart of DoD by days        Chart of DoD by months (hope to adapt MoM instead of DoD)

Sincerely,

Lee, Hong Gwoo

[Greg_Deckler]

I am not understanding your issue. It looks like you have a single chart that has a Month and Day hierarchy that you can drill down and up or are you saying that you want to display both the MoM or DoD at the same time in a single visualization. I do not understand where the ISFILTERED comes into play. Can you please clarify?

[extri13]

First of all, thanks for your interest on the issue.

I illustrate the issue clearly below. Hope it helps your better understanding.

Purpose

1. [In case of day view on X-axis] Chart shows DoD growth rate
2. [In case of month view on X-axis] Chart shows MoM growth rate
3. Above two are occured in same chart
4. and X-axis is with date/time type column which is automatically hierarhied by Power BI as usual

Expected Solution

1. Any DAX measure (like ISFILTERED) can switch DoD and MoM Measures by drill down/up to month and day
2. Any formula with DAX measure which can calculate DoD and MoM simultaneously 

Sincerely,

Lee, Hong Gwoo

[Greg_Deckler]

OK, I'm pretty sure I get it now, can you post your formulas for DoD and MoM?

[extri13]

Below is the formulars.

Thanks in advance for your kindness.

• Revenue (DoD %) = DIVIDE([Revenue (Today)]-[Revenue (Today -1)], [Revenue (Today -1)])
  Revenue (Today) = CALCULATE([Revenue (SUM)], DATEADD ( LASTDATE ( 'Dates'[Dates] ), 0, DAY )) +0
  Revenue (Today -1) = CALCULATE([Revenue (SUM)], DATEADD ( LASTDATE ( 'Dates'[Dates] ), -1, DAY ))

• Revenue (MoM %) = DIVIDE ([Revenue (Month)] - [Revenue (Month-1)], [Revenue (Month-1)])
  Revenue (Month) = TOTALMTD([Revenue (SUM)],'Dates'[Dates])
  Revenue (Month-1) = TOTALMTD([Revenue (SUM)],PREVIOUSMONTH('Dates'[Dates]))

• Revenue (SUM) = SUMX('Revenue',[Revenue])

BR,

Lee

[Greg_Deckler]

@extri13 - Something I just found that you might want to vote for (although it is already started): 

https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/13397166-better-date-range-filter...

[Greg_Deckler · Accepted solution]

OK, and I'm just confirming and talking this out in my head, what we need to do is either:

1. Find a way to switch up which measure we are using in the chart based upon the filter context for that chart
2. Find a way to use a single formula for calculating both DoD and MoM
3. Find a way to combine DoD and MoM into a single aggregate measure that gets filtered based upon the filter context of the chart

Honestly, I'm not sure any of those are possible but I'll spend some cycles noodling on it. The one that strikes me as the most plausible is #2 using something like PARALLELPERIOD maybe? Not thinking of an easy solution.

[extri13]

Thank you very much.

It seems like there is no right answer for my need based on the opinion of you who is one of the most professional user on this board I've seen so far.

I am really really impressed by your kindness and passion for BI from your answer.

Also, it is still a good answer that there is no solution. 

Thanks again 

Lee, Hong Gwoo