Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
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.
@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
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
41 | |
19 | |
19 | |
15 | |
15 |
User | Count |
---|---|
49 | |
26 | |
22 | |
17 | |
16 |