cancel
Showing results for
Did you mean: New Member

## Period to Date for Previous Year

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],
)
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.

3 REPLIES 3  Community Support

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.  Super User

@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 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
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 Features || Datamarts: https://youtu.be/8tskWsJTEpg || Field Parameters : https://youtu.be/lqF3Wa1FllE?t=70
Time Intelligence Decoded : https://youtu.be/aU2aKbnHuWs&t=145s
Did I answer your question? Mark my post as a solution! Appreciate your Kudos !! Proud to be a Super User! !!
Dashboard of My Blogs !! Connect on Linkedin !! Subscribe to my youtube Channel !!
Want To Learn Power BI | Beginners !! Advance Concepts !! Power BI For Tableau User !! New Member

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],
'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],
)
RETURN
Result``````

It works for my month granularity series, But not my daily granularity series  Announcements #### The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.  