Dynamic Quarter Selection

[Anonymous]

Hi Everyone,
I am creating calculated measures to report out on some seats data. I am trying to use the fiscal qtr filter in a calculated measure to calculate "Current Quarter" and "last quarter" like this,

Last Quarter Downloads = CALCULATE(DISTINCTCOUNT(cce_pro_downloads_enriched[content_download_id]),cce_pro_downloads_enriched[Fiscal_Quarters] = "Q3-2019")
I can use "dateadd" for date formats and number formats. How do I make this quarter selection dynamic for text formats("Q4-2019"), so that I don't have to make this change in the formula, every quarter? 

[Anonymous]

Hi Everyone,
I am creating calculated measures to report out on some seats data. I am trying to use the fiscal qtr filter in a calculated measure to calculate "Current Quarter" and "last quarter" like this,

Last Quarter Downloads = CALCULATE(DISTINCTCOUNT(cce_pro_downloads_enriched[content_download_id]),cce_pro_downloads_enriched[Fiscal_Quarters] = "Q3-2019")
I can use "dateadd" for date formats and number formats. How do I make this quarter selection dynamic for text formats("Q4-2019"), so that I don't have to make this change in the formula, every quarter? 

[Greg_Deckler]

Any chance we could see some sample raw data? Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

[jdbuchanan71]

Hello @Anonymous 
Give these a try.  I beleive they will work how you want.

Current Quarter = 
VAR _Current = "Q" & FORMAT ( TODAY(),"q-yyyy")
RETURN CALCULATE(DISTINCTCOUNT(cce_pro_downloads_enriched[content_download_id]),cce_pro_downloads_enriched[Fiscal_Quarters] = _Current)

Last Quarter = 
VAR _Last = "Q" & FORMAT ( EOMONTH( TODAY(), -3 ),"q-yyyy")
RETURN CALCULATE(DISTINCTCOUNT(cce_pro_downloads_enriched[content_download_id]),cce_pro_downloads_enriched[Fiscal_Quarters] = _Last)

If this solves your issues please mark it as the solution. Kudos 👍 are nice too.
 

[Anonymous]

Hey @jdbuchanan71 , @Nathaniel_C 
Thank you for your reply. I tried it and it kinda works but there are some issues. I tweaked your formula and created these measures (fiscal quarter format: 2019-Q4):

Current Quarter Downloads =

var _current = FORMAT([last date],"yyyy-\Qq")

return CALCULATE(SUM('cce-downloads_asset_type'[downloads]),FILTER('cce-downloads_asset_type','cce-downloads_asset_type'[fiscal_quarter] = _current))
Last Quarter Downloads = 

VAR _Last = FORMAT ( EOMONTH( TODAY(), -3 ),"yyyy-\Qq")

return CALCULATE(SUM('cce-downloads_asset_type'[downloads]),FILTER('cce-downloads_asset_type','cce-downloads_asset_type'[fiscal_quarter] = _Last))
But these do not match the values I get by using these filters:

Q4 = CALCULATE(SUM('cce-downloads_asset_type'[downloads]),'cce-downloads_asset_type'[fiscal_quarter] = "2019-Q4")

Q3 = CALCULATE(SUM('cce-downloads_asset_type'[downloads]),'cce-downloads_asset_type'[fiscal_quarter] = "2019-Q3")
The results for these measures look like this (This is correct).

So I can create a comparison which looks like this:

But after using the dynamic qtr measures, Although the totals are same, the result looks as follows as I cannot create the timely comparison.

[jdbuchanan71]

@Anonymous 

Can you share your .pbix file?

[Anonymous]

Hey @jdbuchanan71 ,
Thanks for willing to help me out. here's the link to the report. Please let me know if you aren't able to open it.
https://app.powerbi.com/groups/me/reports/6c35b5ab-73df-4064-9469-d855cd2a12dc?ctid=fa7b1b5a-7b34-43...
looking forward to hearing from you!
Best,
Akash

[jdbuchanan71]

Hi @Anonymous 

I wouldn't be able to log into your PowerBI report.  I was wondering if you could upload your PowerBI desktop file (.pbix) to OneDrive or DropBox and share the link here so I can see the data and the structure of the model.

[Anonymous]

HI  @jdbuchanan71 ,
I have uploaded the file to my google drive. here's the link: https://drive.google.com/openid=1HA30k083pI1bcWIMsczM2Z6gKAB1OXEG
Hope this works!
Best,
Akash

[jdbuchanan71 · Accepted solution]

It was using LASTDATE('Asset type'[As_of_date]) In the Current Quarter downloads that was giving you the problem but we also didn't need the filter statement.

Current Quarter = 
VAR _current = FORMAT (  TODAY() ,"yyyy-\Qq" )
RETURN
CALCULATE( SUM ( 'Asset type'[Downloads] ), 'Asset type'[Fiscal_quarter] = _current )

Last Quarter = 
var _last = FORMAT ( EOMONTH( TODAY(), -3 ),"yyyy-\Qq")
RETURN
CALCULATE( SUM ( 'Asset type'[Downloads] ), 'Asset type'[Fiscal_quarter] = _last )

For your second question a couple of notes.

First you should always write a measure rather than just pulling a value into a visual so for downloads we have.

Total Downloads = SUM ( 'Asset type'[Downloads] )

This lets us use that in further measures, prior week for example:

PW Downloads = CALCULATE ( [Total Downloads] , DATEADD ( 'Asset type'[As_of_date] , -7 , DAY ) )

Then we can put them together for a week over week change

WoW downloads = [Total Downloads] - [PW Downloads]

And again for the % change

WoW % Change = DIVIDE ( [WoW downloads], [PW Downloads] )

My updated file is attached for you to take a look at.

If this solves your issues please mark it as the solution. Kudos 👍 are nice too.

[Anonymous]

This was great!! Thanks a ton!!
Best,
Akash.