Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Hi All,
Within the business our Financial Year starts 1st May each year so for example this current week is Year Week 202401 (the year & the week number). To calculate anything for prior year, we use the below DAX and do this for all our calculations. I believe it is not helping us and is pushing our capacity limits which we always seem to be hitting when loading in a lot of visuals with this on a page. Does anyone know a better way to calculate the below please? We have tried a few before that don't work with our Dynamic Calendar, using sameperiodlastyear doesn't work for example.
So the table is Calendar, it uses AFBYearWeek column which for example is 202401 this week our first week of the new financial year. It is calculating the DAX measure Count Of Products in the below example, and -100 weeks to get the figure from the year before. But this being used to go back 100 rows in a column each time its loaded in can't be doing it much good. Any help is appreciated. Thank You, Michael
Count Of Products Prior Year = sumx(SUMMARIZE('Calendar','Calendar'[AFBYearWeek],"PYWS",CALCULATE( IFERROR( ([Count Of Products]),0),FILTER(ALL('Calendar'),'calendar'[AFBYearWeek] = max('Calendar'[AFBYearWeek])-100))),[PYWS]) |
Count Of Products Prior Year =
VAR _fiscal_year = YEAR(EDATE( _today_date, 5))
VAR _fiscal_year_start = DATE ( _fiscal_year - 1, 05, 01)
VAR _fiscal_year_end = DATE ( _fiscal_year, 04, 30)
RETURN
CALCULATE(
[Count Of Products],
DATESBETWEEN('Calendar'[Date], _fiscal_year_start, _fiscal_year_end)
)
User | Count |
---|---|
57 | |
48 | |
18 | |
16 | |
15 |
User | Count |
---|---|
116 | |
42 | |
41 | |
27 | |
21 |