Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Fiscal Quarter

Hi,

 

I hope you're fine. I trying to calculate TOTALQTD for my financial calendar. I've read a lot but can't find a way to solve this...

 

My fiscal calendar is: 

Q1: Aug-Oct

Q2: Nov-Jan

Q3: Feb-Apr

Q4:May-July

 

My calendar table is here. 

 
 

I want to calculate the measure [projects submitted] for current QTD. 

 

Also, I have a query. I guess the TOTALQTD value can be filtered by the Academic Year and Quarter from a slicer/filter? So the user can choose the date, but if not selected it will always choose the latest QTD? For natural quaters, for my TOTALQTD to show the current QTD, I had to use "Today" (Using Date column) to show current QTD, otherwise it went blank. 

 

Thanks

 

Thanks a lot!

 

Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    Hi all,

    Finally I come up with the solution!

    Here the solution that worked for me. I had to create calculated columns in my calendar table to create the fiscal quarters:

    Start of QTR = IF('Calendar'[Month Number]=8||'Calendar'[Month Number]=9||'Calendar'[Month Number]=10||'Calendar'[Month Number]=11||'Calendar'[Month Number]=12,DATE(YEAR('Calendar'[Date]),'Calendar'[Month of the quarter],1),IF('Calendar'[Month Number]=1,DATE(YEAR('Calendar'[Date])-1,'Calendar'[Month of the quarter],1),IF('Calendar'[Month Number]=2||'Calendar'[Month Number]=3||'Calendar'[Month Number]=4||'Calendar'[Month Number]=5||'Calendar'[Month Number]=6||'Calendar'[Month Number]=7,DATE(YEAR('Calendar'[Date]),'Calendar'[Month of the quarter],1))))
     
    End of Qtr = EOMONTH('Calendar'[Start of QTR],2)
     
    Then I created the DAX measures:
    QTD Projects Submitted = CALCULATE([Projects Submitted],DATESBETWEEN('Calendar'[Date],MIN('Calendar'[Start of QTR]),MAX('Calendar'[End of Qtr])))
     
    Hope this helps. 

     

9 Replies

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hi Greg,

       

      Thanks for your prompt response. I did read the post, and... I couldn't get it. the measure (qtd new) behaves as TOTALYTD (see the table) even if I changed the formula to "Quarters". 

       

      This is what I sued for QTD new:

      QTD new =

          var MaxYear = MAX('Calendar'[Inital year])

          var MaxQuarter = MAX('Calendar'[Academic Quarter])

          var TmpTable = CALCULATETABLE('Calendar',ALL('Calendar'[Inital year]),ALL('Calendar'[Academic Quarter]))

          return SUMX(FILTER(TmpTable,'Calendar'[Inital year]=MaxYear && 'Calendar'[Academic Quarter] <= MaxQuarter),[Projects Submitted])

       

      This table should look like this

       

      Quarter/monthProjects submittedQTD newYTD submitted
      1777
      Aug373
      Sept376
      Oct177
      2111118
      Nov61113
      Dec11114
      Jan41118

       

      thanks for you help.

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    6 years ago

    Anonymous , Add the following to your date table 

    Start of Year = STARTOFYEAR(Dates[Date],"7/31")
Qtr No = "Q"& QUOTIENT(DATEDIFF(Dates[Start of Year], Dates[Date],MONTH),3)+1
Start of Qtr = date(year(Dates[Start of Year]), month(Dates[Start of Year])+Dates[Add Qtr],1)
Qtr Rank = RANKX(ALL(Dates),Dates[Strat of Qtr],,ASC,Dense)
Qtr = QUOTIENT(DATEDIFF(Dates[Start of Year], Dates[Date],MONTH),3)+1

     

    Create measures like this

    This Qtr = CALCULATE([Total Value], FILTER(ALL(Dates), Dates[Qtr Rank] =max(Dates[Qtr Rank])))
last Qtr = CALCULATE([Total Value], FILTER(ALL(Dates), Dates[Qtr Rank] =max(Dates[Qtr Rank])-1))

     

    To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
    https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
    https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
    https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Thanks. I'm trying this. 

      What's the formula for 

      Dates[Add Qtr]

       

      Thanks. 

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hi again amitchandak 

       

      I did manage to get ADD QT, and the orther calculated columns. However, the measure This QTD gives an error.

       

      This QTD = CALCULATE([Projects Submitted],FILTER(ALL('Calendar'[Date]),'Calendar'[Qtr Rank]=MAX('Calendar'[Qtr Rank])))
       
      Error: A single value for column Qtr Rank in table "Calendar" cannot be determined. This can happen when a measure formual refers to a column that contains many values withouht specifying an agregation such as min, max, count or sum to get a single results.
       
      If I take out FILTER, as
      CALCULATE([Projects Submitted],ALL('Calendar'[Date]),'Calendar'[Qtr Rank]=MAX('Calendar'[Qtr Rank]))
      Then I have the error that a function MAX has been used in a True/False expression that is used as a table expression.
       
      Also, the column Rank Qtr gives only the value 1... I don't know if this is normal?
       
      any help please? 
      • amitchandak's avatar
        amitchandak
        Icon for Super User rankSuper User
        6 years ago

        Try

        This QTD = CALCULATE([Projects Submitted],FILTER(ALL('Calendar'),'Calendar'[Qtr Rank]=MAX('Calendar'[Qtr Rank])))

  • v-lionel-msft's avatar
    v-lionel-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    Hi Anonymous ,

     

    Like this?

    Measure = 
CALCULATE(
    SUM(Sheet2[Value]),
    FILTER(
        ALL(Sheet2),
        Sheet2[Financial _Quarter] = SELECTEDVALUE(Sheet2[Financial _Quarter]) && Sheet2[Financial_Date] <= SELECTEDVALUE(Sheet2[Financial_Date])
    )
)

     

    Best regards,
    Lionel Chen

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.