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

Anonymous 

Can you share your .pbix file?

  • jdbuchanan71's avatar
    jdbuchanan71
    Super User
    6 years ago

    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.

     

     

      • jdbuchanan71's avatar
        jdbuchanan71
        Super User
        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.