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.
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.
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 |
---|---|
113 | |
97 | |
85 | |
70 | |
61 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |