Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Fiscal quarter to date calculation

Hi everyone,

 

I have a date table with fiscal quarter number defined per our organization.  How can I sum a list of numbers from a separate table according to the fiscal quarters I defined in my calendar table?

 

Many thanks!

Need Help

5 Replies

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    7 years ago

    Hi,

     

    There should be a relationship from the Date column of the Other Table to the Date column of the Calendar Table.  In your visual, drag the Fiscual Quarters from the Calendar Table.  Write this measure

     

    =SUM(Data[Amount])

     

    Hope this helps.

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      My question is about a way to create a measure to calculate fiscal quarter to date (versus the default QTD)

      • Ashish_Mathur's avatar
        Ashish_Mathur
        Icon for Super User rankSuper User
        7 years ago

        Hi,

        Assuming your fiscal quarter is from April to March, write these calculated column formula in the Calendar Table.

        Fiscal Year = IF(MONTH(Calendar[Date])<=3,YEAR(Calendar[Date])-1&"-"&YEAR(Calendar[Date]),YEAR(Calendar[Date])&"-"&YEAR(Calendar[Date])+1)

        Month = FORMAT(Calendar[Date],"mmmm")

        In your visual, drag Fiscal Year and Month from the Calendar Table.  Write these measures

        Revenue = SUM(Data[Amount])

        QTD Revenue = CALCULATE([Revenue],DATESBETWEEN(Calendar[Date],EDATE(MIN(Calendar[Date]),-2),MAX(Calendar[Date])))

        Hope this helps.