topic Previous Period Pattern, inconsistent results in DAX Commands and Tips

Previous Period Pattern, inconsistent results

rpiboy_1 — Tue, 27 Aug 2024 17:24:17 GMT

I have two fact tables joined to the same (well formed) date Table, both on 'Date'[Date].

In fact table 1 I'm using the following measure to calculate previous period for a sumed value:

Previous Period Logins = SWITCH( TRUE(), ISINSCOPE( 'Date'[Date] ), CALCULATE( [Logins (sum)], DATEADD( 'Date'[Date], -1, DAY )), ISINSCOPE( 'Date'[Fiscal Month] ), CALCULATE( [Logins (sum)], DATEADD( 'Date'[Date], -1, MONTH )), ISINSCOPE( 'Date'[Fiscal Quarter] ), CALCULATE( [Logins (sum)], DATEADD( 'Date'[Date], -1, QUARTER )), ISINSCOPE( 'Date'[Fiscal Year] ), CALCULATE( [Logins (sum)], DATEADD( 'Date'[Date], -1, YEAR )) )

I applied the same pattern to fact table 2, again a SUM on a value, and instead of returning the previous period, it returns the same value as the 'current' period. I'm super confused as I've checked backwards and forwards and there are no 'differences' other than a different fact table and different facts.

Design Area Avg Previous Period = VAR _area = SUM('Monthly Project Data'[Design Area Avg]) VAR _result = SWITCH( TRUE(), ISINSCOPE( 'Date'[Date] ), CALCULATE( _area , DATEADD( 'Date'[Date], -1, DAY )), ISINSCOPE( 'Date'[Fiscal Month] ), CALCULATE( _area , DATEADD( 'Date'[Date], -1, MONTH )), ISINSCOPE( 'Date'[Fiscal Quarter] ), CALCULATE( _area , DATEADD( 'Date'[Date], -1, QUARTER )), ISINSCOPE( 'Date'[Fiscal Year] ), CALCULATE( _area , DATEADD( 'Date'[Date], -1, YEAR )) ) RETURN _result

I guess the only other thing worth noting is that both facts have monthly granularity, not daily, but that should not matter, as I'm not trying to visualize the data at daily granularity anyway.

Looking for advice on what else I should be considering as to why I'm getting inconsistent results. What am I possibly missing or forgetting about?

Re: Previous Period Pattern, inconsistent results

Greg_Deckler — Tue, 27 Aug 2024 17:45:55 GMT

rpiboy_1 Variables (VAR) despite their name are constants and once calculated remain the same value regardless of anything you might try to do. So, that's the reason for the results you are getting.

Re: Previous Period Pattern, inconsistent results

Anonymous — Wed, 28 Aug 2024 05:37:45 GMT

Hi rpiboy_1 ,

I’d like to acknowledge the valuable input provided by Greg_Deckler . Their initial ideas were instrumental in guiding my approach. However, I noticed that further details were needed to fully understand the issue.

You can refer to my example and make appropriate changes to your measure.

Measure = VAR _result = SWITCH( TRUE(), ISINSCOPE( 'Date'[Date].[Day] ), CALCULATE( SUM('financials'[ Sales]), DATEADD( 'Date'[Date], -1, DAY )), ISINSCOPE( 'Date'[Date].[Month] ), CALCULATE( SUM('financials'[ Sales]) , DATEADD( 'Date'[Date], -1, MONTH )), ISINSCOPE( 'Date'[Date].[Quarter] ), CALCULATE( SUM('financials'[ Sales]) , DATEADD( 'Date'[Date], -1, QUARTER )), ISINSCOPE( 'Date'[Date].[Year]), CALCULATE( SUM('financials'[ Sales]) , DATEADD( 'Date'[Date], -1, YEAR )) ) RETURN _result

If your Current Period does not refer to this, please clarify in a follow-up reply.

Best Regards,

Clara Gong

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: Previous Period Pattern, inconsistent results

rpiboy_1 — Wed, 28 Aug 2024 12:37:33 GMT

Ugh... yeah, I've read that before, but I don't do this work day in and day out, so hard to remember all of the 'details'. Thanks!