Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Dynamic Quarter Selection

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? 
Need Help
Show and Tell
Tips and Tricks
  • jdbuchanan71's avatar
    jdbuchanan71
    6 years ago

    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.

     

10 Replies

  • jdbuchanan71's avatar
    jdbuchanan71
    Super User
    6 years ago

    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's avatar
      Anonymous
      Not applicable
      6 years ago

      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.

       
       
       
       
  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    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?