Previous period sales based on choosen granularity

gumis_rulez — Wed, 30 Oct 2024 12:44:26 GMT

I would like to create a measure which will calculate sales for previous period based on choosen granularity.

I followed: https://www.youtube.com/watch?v=hilfglpKNRQ to create granularity and slicer.

first table is connected with slicer with visual table and this table should recalculate appropriately the sales for previous period based on selection.

I started to create a measure:

Sales Previous Period = VAR SelectedType = SELECTEDVALUE('hlp_Dynamic Date Selections'[Type]) RETURN SWITCH( TRUE(), SelectedType = "Y", CALCULATE( [Sales] ) )

but when I refer to dimCalendar or use time intelligence functions the result does not show. How to achieve such functionality?

https://drive.google.com/file/d/15UslwRjGZy-yEifeGWuSPq7_BPcB_yV-/view?usp=sharing

Re: Previous period sales based on choosen granularity

johnt75 — Wed, 30 Oct 2024 14:00:49 GMT

You need to remove the filters on the helper table as well as manipulating the dates in the date table.

Sales Previous Period = VAR SelectedType = SELECTEDVALUE('hlp_Dynamic Date Selections'[Type]) VAR PrevYear = DATEADD( dimCalendar[Date], -1, YEAR ) VAR PrevQuarter = DATEADD( dimCalendar[Date], -1, QUARTER ) VAR PrevMonth = DATEADD( dimCalendar[Date], -1, MONTH ) VAR PrevWeek = DATEADD( dimCalendar[Date], -7, DAY ) VAR PrevDay = DATEADD( dimCalendar[Date], -1, DAY ) VAR Result = SWITCH( SelectedType, "Y", CALCULATE( [Sales], PrevYear, REMOVEFILTERS( 'hlp_Dynamic Date Selections' ) ), "Q", CALCULATE( [Sales], PrevQuarter, REMOVEFILTERS( 'hlp_Dynamic Date Selections' ) ), "M", CALCULATE( [Sales], PrevMonth, REMOVEFILTERS( 'hlp_Dynamic Date Selections' ) ), "W", CALCULATE( [Sales], PrevWeek, REMOVEFILTERS( 'hlp_Dynamic Date Selections' ) ), "D", CALCULATE( [Sales], PrevDay, REMOVEFILTERS( 'hlp_Dynamic Date Selections' ) ) ) RETURN Result

topic Previous period sales based on choosen granularity in DAX Commands and Tips

Previous period sales based on choosen granularity

Re: Previous period sales based on choosen granularity