The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event! Join us in Stockholm, Sweden from September 24-27, 2024.
2-for-1 sale on June 20 only!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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.
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
124 | |
105 | |
83 | |
64 | |
58 |
User | Count |
---|---|
254 | |
120 | |
115 | |
100 | |
78 |