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" li...
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.

     

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.