Changing the measure granularity when you display chart

[RS_123]

Hi,

I am building a financial dashboard that shows the financial performance of a company, comparing the quarterly performance to  year-ago quarter and prior quarter. The fiscal year of the company starts on July 1st and ends on June 30th. Since the company reports only quarterly, the financial table only has the end of the quarter date in the date column. e.g. March 30, 2016, March 30, 2020. I have created a separate date table that has contiguous date starting from July 1st 2016 (beginning of quarterly date in the financial table) to March 30, 2020 (end of the quarterly date in the financial table). I have setup bidirectional bidirectional relationship between the date table and the financial table. 
I have written a measure to calculate the YoY increase in subscription revenue as stated below:

YoY Subs =

VAR _current = SELECTEDVALUE('IS'[Subscription])

VAR _Prior = [PY Subscription Rev]

Return

IF ( ISBLANK(_Prior), blank(),

DIVIDE(_current, _Prior) - 1

)

I have setup a calculated column called Year Month Number to identify prior year quarter and prior quarter.

PY Subscription Rev =

// CALCULATE( SELECTEDVALUE('IS'[Subscription]), DATEADD('IS'[Date], -1, YEAR))

VAR _PriorYMN = SELECTEDVALUE('IS' [Year Month Number]) - 100

// Use ALL to remove the incoming filter context. Find the YMN that matches PriorQYMN

// and evaluate the expression. Does not work if you use ALL ('IS'[Date])

Return

CALCULATE( VALUES('IS'[Subscription]), 'IS'[Year Month Number] = _PriorYMN,

ALL ('IS')

)

I am using YoY Subs measure in a line and stacked column chart.  When I select quarters from the filter, the chart shows YoY  line on the secondary axis as shown below.

YoY Subs

When I move up the hierarchy to the year in the chart, the YoY subs measure does not work. Please see screenshot below. 

I presume the chart's behavior is due to lack of proper granularity in the YoY subs measure. How do I change the YoY subs DAX code to recognize when the user moves up to the yearly granularity, the measure has to calculate the YoY growth for annual subscription revenue? The quarterly subscription is a column in the financial table called 'IS' [Subscription] and the there are no annual subscription in the column. To calcuate the annual subscription revenue, one needs to sum of the quarterly subscription revenue.

Any help would be greately appreciated.

[amitchandak · Accepted solution]

@RS_123 , You can use trailing year  measure or sameperiodlastyear

https://docs.microsoft.com/en-us/dax/sameperiodlastyear-function-dax

Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))

Last Year Sales = CALCULATE(SUM(Sales[Sales Amount]),sameperiodlastyear('Date'[Date]))

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-Y...

Power BI — YTD Questions — Time Intelligence 1–5
https://medium.com/@amitchandak.1978/power-bi-ytd-questions-time-intelligence-1-5-e3174b39f38a
Power BI — QTD Questions — Time Intelligence 2–5
https://medium.com/@amitchandak.1978/power-bi-qtd-questions-time-intelligence-2-5-d842063da839
Power BI — MTD Questions — Time Intelligence 3–5
https://medium.com/@amitchandak.1978/power-bi-mtd-questions-time-intelligence-3-5-64b0b4a4090e

Appreciate your Kudos.

[lbendlin]

You don't need a bidirectional relationship on this. The Dates table needs to control the facts table.

In your visual use the date fields from the Dates table, not the facts table.