Forum Discussion

Tuan's avatar
Tuan
Icon for Helper III rankHelper III
5 years ago

Fiscal Calendar - Quarter To Date

I'm having trouble with my Quarter to Date formula not working. I'm trying to make a dynamic QTD formula.

This is what I came up with. I'm guessing if there no sales on the specific date, there are no visible sales.

 

QTD $ = 
VAR CurrentQuarter =
CALCULATE (
       MAX (DIM_DATE[fin_quarter_no] ),
       DIM_DATE[financial_date] = TODAY ()
 )
RETURN
    CALCULATE ( [Demand $], DIM_DATE[fin_quarter_no] = CurrentQuarter

 

 

Need Help

2 Replies

  • Tuan's avatar
    Tuan
    Icon for Helper III rankHelper III
    5 years ago

    I think i fixed it using <= sign. If anyone has a better method i would love to know.

     

    DIM_DATE[financial_date] <= TODAY ()

     

    It works with my Quarter to Date but not my month to date.

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

    Tuan , If your QTR start with Jan, Apr, Jul, Oct (irrespective of which qtr no they are), You can use datesQTD with date table

     

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

    Last year same QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(dateadd('Date'[Date],-1,Year)))

     

    for any other you need Qtr start date and Qtr Day and Qtr rank column

    new columns in date tabke 

    Qtr Start Date = DATEADD(STARTOFYEAR('Date'[Date],"4/30"),QUOTIENT(DATEDIFF('Date'[Start Of Year], 'Date'[Date],MONTH),3)*3,MONTH) //choose end of year of your choice in place for 4/30
    Qtr Day = DATEDIFF('Date'[Qtr Start Date],'Date'[Date],Day)+1
    Qtr Rank = RANKX(all('Date'),'Date'[Qtr Start date],,ASC,Dense)

     

    measure like
    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 Day] <=max([Qtr Day])))
    Last QTD = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Qtr Rank]=max('Date'[Qtr Rank])-1 && [Qtr Day] <=max([Qtr Day])))

     

    Creating Financial Calendar - From Any Month
    https://community.powerbi.com/t5/Community-Blog/Creating-Financial-Calendar-Decoding-Date-and-Calendar-1-5-Power/ba-p/1187441

     

    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 :radacad sqlbi My Video Series Appreciate your Kudos.

     

    Please provide your feedback comments and advice for new videos
    Tutorial Series Dax Vs SQL Direct Query PBI Tips
    Appreciate your Kudos.