Previous Period Pattern, inconsistent results

[rpiboy_1]

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?

[Anonymous]

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.

[Greg_Deckler · Accepted solution]

@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.

[rpiboy_1]

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!