topic Re: Calculate a previous period Sum in DAX Commands and Tips

Calculate a previous period Sum

spartan27244 — Thu, 17 Jun 2021 18:04:36 GMT

As in the example on this page SAMEPERIODLASTYEAR function (DAX) - DAX | Microsoft Docs

= CALCULATE(SUM(ResellerSales_USD[SalesAmount_USD]), SAMEPERIODLASTYEAR(DateTime[DateKey]))

I too am trying to do the same, my measuer formula is as so

PrevPrdPlanPaid = CALCULATE(Sum(MedClaims[PlanPaid]), SAMEPERIODLASTYEAR(Dates[Date]))

However my visual always shows a blank, when it should show for example in 2019-FY 10,503,949.37 i.e. the 2018-FY value, etc. I know this seems point less but the number will be used to calculate a rate of change which is simple math once I can get the number. I have attached PBX file, well apparently I cannot attach a file so here is the onedrive link

https://mycatalyst-my.sharepoint.com/:u:/p/ltaylor23/ERyJGLMYHB9NsQO9pcEdlT4BYWfQrOR8-4VlIpYei0poqg?e=s7JdhP

My data model is very simple, note the Date table is Marked as a Date Table.

Re: Calculate a previous period Sum

CNENFRNL — Thu, 17 Jun 2021 20:32:02 GMT

For most seansoned DAX users, both cross filtering direction (bi-directions) in data model is time bomb rather than cure-all. At least for me, I rely on mono-directional relations in data model.

Re: Calculate a previous period Sum

spartan27244 — Wed, 23 Jun 2021 14:27:44 GMT

By removing the bidirectional filter, none of the other visuals are calculated properly

Re: Calculate a previous period Sum

spartan27244 — Thu, 24 Jun 2021 12:47:21 GMT

Looks like this works

PrevPlanPaid =

Var D = DATESBETWEEN(Dates[Date], DateAdd(firstdate(Dates[Date]), -1, year), DATEADD(LASTDATE(Dates[Date]), -1, YEAR))

return CALCULATE(Sum(MedClaims[PlanPaid]), REMOVEFILTERS(ReportPeriods), D)