Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi All,
I'm trying to make a matrix of previous year's for non contiguous dates. This is going into a larger formula / matrix that uses this to set conditional formatting, but I've broken it down and this is the last piece I need to figure out.
Here's the forumla for my measure to calculate previous year's sales:
Works fine when evaluated against the total including split by customer, but within the table or matrix each row for year shows either 0 or blank (see second screenshot)
I think my brain is just mush at this point so any help is greatly appreciated. Thanks!
Solved! Go to Solution.
Hello @PANDAmonium
I have seen strange behavior when using time intelligent functions (PARALLELPERIOD) without a date table that extends from Jan 1st to Dec 31st.
You can add a basic date table using the following DAX code.
Dates = VAR DateRange = CALENDARAUTO() RETURN ADDCOLUMNS( DateRange, "Year",YEAR ( [Date] ), "Month", FORMAT ( [Date], "mmmm" ), "MonthNum", MONTH ( [Date] ), "Month Year", FORMAT ( [Date], "mmm-yyyy"), "MonthYearNum", YEAR ( [Date] ) * 100 + MONTH ( [Date] ), "Quarter Year", "Q" & FORMAT ( [Date], "q-yyyy" ), "QtrYearNum", YEAR ( [Date] ) * 100 + VALUE ( FORMAT ( [Date], "q" ) ) )
Then you link the Dates[Date] field into 'Table'[Date].
Next, under modeling, mark the Dates table as a date table.
Then try the measure like this.
PY Value = CALCULATE ( SUM('Table'[Value]), SAMEPERIODLASTYEAR ( DATES[Date] ) )
Hello @PANDAmonium
I have seen strange behavior when using time intelligent functions (PARALLELPERIOD) without a date table that extends from Jan 1st to Dec 31st.
You can add a basic date table using the following DAX code.
Dates = VAR DateRange = CALENDARAUTO() RETURN ADDCOLUMNS( DateRange, "Year",YEAR ( [Date] ), "Month", FORMAT ( [Date], "mmmm" ), "MonthNum", MONTH ( [Date] ), "Month Year", FORMAT ( [Date], "mmm-yyyy"), "MonthYearNum", YEAR ( [Date] ) * 100 + MONTH ( [Date] ), "Quarter Year", "Q" & FORMAT ( [Date], "q-yyyy" ), "QtrYearNum", YEAR ( [Date] ) * 100 + VALUE ( FORMAT ( [Date], "q" ) ) )
Then you link the Dates[Date] field into 'Table'[Date].
Next, under modeling, mark the Dates table as a date table.
Then try the measure like this.
PY Value = CALCULATE ( SUM('Table'[Value]), SAMEPERIODLASTYEAR ( DATES[Date] ) )
Awesome, yeah, that worked. Thank you very much!
It feels so good just to finally blink, lol.
Actually...I might just need to switch it to a *:1 with a single direction filter. 1 min.
As soon as I split the table or matrix by customer it throws an error.