Forum Discussion

damit23183's avatar
damit23183
Microsoft Employee
5 years ago

Time Intelligence DAX formula

Hi,

I do have a COUNT measure and now i would like to calculate count based on following criteria ;

 

Current year Count which is YTD till now (I got that by TOTALYTD syntex),

Count till now based on Month,

Count till now based on Quarter

 

same concept i would like to apply for all fiscal Year columns which is different than caledar year.

So, want to count based on year to date for fiscal year, Month to date for fiscal month and Quarter to date for Fiscal quarter.

 

NOTE: Calendar year is start from january to December. Fiscal year is starting from October to September. I have all fiscal year columns. 

 

Thanks

 

Need Help

5 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    damit23183 , For Oct-Sep year totalytd a and dates ytd should work

     

    YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"9/30"))
    Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"9/30"))

     

    QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(('Date'[Date]))) // Standard Qtr
    Last QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(dateadd('Date'[Date],-1,QUARTER)))

     

    // Non standard

    column needed

    Qtr Rank = RANKX(all('Date'),'Date'[Qtr Start date],,ASC,Dense)

     

    measure
    This Qtr = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Qtr Rank]=max('Date'[Qtr Rank])))
    Last Qtr = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Qtr Rank]=max('Date'[Qtr Rank])-1))

    This QTD = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Qtr Rank]=max('Date'[Qtr Rank]) && [Qtr Month No ] <=max([Qtr Month No ])))
    Last QTD = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Qtr Rank]=max('Date'[Qtr Rank])-1 && [Qtr Month No ] <=max([Qtr Month No ])))

     

     

    MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
    last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))

     

    Power BI — Year on Year with or Without Time Intelligence
    https://medium.com/@amitchandak.1978/power-bi-ytd-questions-time-intelligence-1-5-e3174b39f38a
    Power BI — Qtr on Qtr with or Without Time Intelligence
    https://medium.com/@amitchandak.1978/power-bi-qtd-questions-time-intelligence-2-5-d842063da839
    Power BI — Month on Month with or Without Time Intelligence
    https://medium.com/@amitchandak.1978/power-bi-mtd-questions-time-intelligence-3-5-64b0b4a4090e

    • damit23183's avatar
      damit23183
      Microsoft Employee
      5 years ago

      Thanks Amit for your response.

       

      I will try to apply all logic that you have mentioend and see how it works.

       

      However, i dont see or may be i have not understood on how to count with Fiscal year, Month and QUarter? 

       

      I feel Fiscal year calculation is more challenging for me than calendar year calculation.

       

      Thanks

    • damit23183's avatar
      damit23183
      Microsoft Employee
      5 years ago

      Hi Vijay,

       

      THank you for your response. 

       

      Yes YTD was already working challenge for me is FISCAL YEAR, Month, Quarter calculation.

       

      Thanks

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi damit23183,

    You can also take a look at the following blog to use the date function to achieve time intelligence. They are more agility than time intelligence functions and not require a calendar table and they can be nested with other functions:

    Time Intelligence "The Hard Way" (TITHW)  
    Regards,

    Xiaoxin Sheng