Period to Date for Previous Year

[joelmsherman]

Hello there,

I have a data model as follows:

I created a measure for a card visual to display the most recent period-to-date totals of a base measure, [TotalActuals]:

ActualsPeriodTD_Current_Last = 
VAR LastDataDate = MAX ('FACT Data'[Date] )
VAR Result = 
    CALCULATE(
        [ActualsPeriodTD_Current],
        'DIM Date'[Date] = LastDataDate
    )
RETURN
    Result

This measure references another measure built off a disconnected table to let the user select what kind of "to-date" period they want to analyze (i.e. calendar-to-date, fiscal-to-date, or quarter-to-date).

ActualsPeriodTD_Current = 
VAR PeriodType = SELECTEDVALUE ('@DynamicTimeIntellSelection'[Type] )
VAR Calendar = CALCULATE ( [TotalActuals], DATESYTD ( 'DIM Date'[Date] ) ) 
VAR Fiscal = CALCULATE ( [TotalActuals], DATESYTD ('DIM Date'[Date], "6-30" ) )
VAR Quarter = CALCULATE ( [TotalActuals], DATESQTD ('DIM Date'[Date] ) )
VAR Result =
    SWITCH (
        PeriodType,
        "Calendar YTD", Calendar,
        "Fiscal YTD", Fiscal,
        "Quarter TD", Quarter,
        BLANK()
    )
VAR FirstDateInPeriod = MIN( 'DIM Date'[Date] )
VAR LastDateWithData = MAX ( 'FACT Data'[Date] )
RETURN
    IF ( FirstDateInPeriod <= LastDateWithData, Result )

 The measure works, as shown in my evaluation context below.  The most recent (Feb 2022) calendar year-to-date total of [TotalActuals] is 104,448.

My Question: How can I write another measure that returns the calendar year-to-date value for the same month, just 1 year earlier? So in my evaluation context, I want the card to return 118,969.  Further, I'd like to compute yet another measure for a third card that does the year-over-year percent diff for these two measures.  

Any help is much appreciated.

[v-yangliu-msft]

Hi  @joelmsherman,

You can modify dax to the following form:

Calendar =
SUMX (
    FILTER (
        ALL ( DIMDATE ),
        'DIM Date'[Date]
            >= DATE ( YEAR ( MAX ( 'DIM Date'[Date] ) ) - 1, 1, 1 )
            && 'DIM Date'[Date]
                <= DATE ( YEAR ( MAX ( 'DIM Date'[Date] ) ) - 1, MONTH ( MAX ( 'DIM Date'[Date] ) ), DAY ( MAX ( 'DIM Date'[Date] ) ) )
    ),
    [TotalActuals]
)

Best Regards,

Liu Yang

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

[amitchandak]

@joelmsherman , as your qtr start from July, Oct, Jan, and Apr. datesqtd should work for you

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)))
Last year same QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(ENDOFQUARTER(dateadd('Date'[Date],-1,Year))))
trailing QTR = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,QUARTER))
trailing 4 QTR = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-4,QUARTER))

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

alternate

Day of Year =datediff([Year Start date] , [Date],Day) +1

YTD= CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Year Rank]=max('Date'[Year Rank]) && 'Date'[Day of Year] <= Max('Date'[Day of Year]) ))
LYTD = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Year Rank]=max('Date'[Year Rank])-1 && 'Date'[Day of Year] <= Max('Date'[Day of Year])))

Skip and watch

Power BI for Beginners - Time Intelligence, DATESMTD, DATESQTD, DATESYTD, Week On Week, Week Till Date, Custom Period on Period,
Custom Period till date: https://youtu.be/aU2aKbnHuWs

Power BI — Year on Year with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-ytd-questions-time-intelligence-1-5-e3174b39f38a
https://www.youtube.com/watch?v=km41KfM_0uA
Power BI — Qtr on Qtr with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-qtd-questions-time-intelligence-2-5-d842063da839
https://www.youtube.com/watch?v=8-TlVx7P0A0

[joelmsherman]

Thank you but none of those general time intell solutions seem to work.  I believe my issues is that my data has different granularities.  For example, Expenses and Revenue facts come in at month level and are dated "mm/1/yyyy".  But other DIM Series come in at day level ("mm/dd/yyyy").  So when I created these measures:

ActualsPeriodTD_Previous = 
VAR FirstDateInPeriod = MIN( 'DIM Date'[Date] )
VAR LastDateWithData = MAX ( 'FACT Data'[Date] )
VAR Result = 
CALCULATE (
    [ActualsPeriodTD_Current],
    DATEADD (
        'DIM Date'[Date], -1, YEAR
    )
)
RETURN
IF ( FirstDateInPeriod <= LastDateWithData, Result )

And this one for the card:

ActualsPeriodTD_Previous_Last = 
VAR LastDataDate = MAX ('FACT Data'[Date])
VAR Result = 
    CALCULATE(
        [ActualsPeriodTD_Previous],
        'DIM Date'[Date] = LastDataDate
    )
RETURN
    Result

 It works for my month granularity series, 

But not my daily granularity series